Adding a Sequence Number to Transactions Based on Date and a Unique ID

[thabounceisback]

Greetings!

I have done this before, I just cannot remember the most efficient way to do it.

I have a set of transactions with a Unique ID field and a date. I want to add a field based that gives me the sequence of events for each Unique ID in order of when it happened.

For example, if customer X has 6 transactions, the sequence field would have a number (1-6) in each record that corresponds to the order in which those transactions occurred. The first transaction would have the number 1, the second would have the number 2 and so on.


What is the most efficient way of accomplishing this?


I appreciate the help! Also, I apologize if this has been covered a lot here. I tried to search for a thread that would help, but came up with a bunch of sequencing threads that did not apply.

Thanks,
Kurt

 

[vbaInet]

The sequence shouldn't matter if you have a unique Number field that automatically incremented.

Can you tell us what you want to use the sequence for?

 

[thabounceisback]

Sure!

There is a third field with quantities in it. Using this, the unique ID and the sequence field, I am going to create a field that tells me how that number has changed since the last transaction.

So if customer A has 3 transactions with 10, 20 and 30 in the quantity, I want a new field that has 10, 10 and 10 in it as a representation of how much the quantity has change between that transaction and the previous transaction.

I thought this would be easiest with a sequence field as it will always be 1 number higher than the previous transaction, whereas a date can vary in a less predictable fashion.

Let me know if my thinking is off on that. Thanks!

 

[vbaInet]

So if customer A has 3 transactions with 10, 20 and 30 in the quantity, I want a new field that has 10, 10 and 10 in it as a representation of how much the quantity has change between that transaction and the previous transaction.

I don't follow that bit. Can you elaborate?

Is the unique field an Autonumber field?