Search code examples
sqlms-access

Apply Sequential Labels To Records in Access

I'm sure this is simple enough to do but I can't seem to frame the question right in Google. I have order data from an ecommerce store, I want to be able to do a select query to label a customer's 1st, 2nd, 3rd, etc orders (based on date) so I can map out other metrics downstream.

Example Source Data:

customer id order # date
123 abc1 3/1/22
187 abc2 3/3/22
123 abc3 3/17/22
165 abc4 3/19/22
123 abc5 3/22/22

Desired Result:

customer id order # date Order Sequence
123 abc1 3/1/22 First Order
187 abc2 3/3/22 First Order
123 abc3 3/17/22 Second Order
165 abc4 3/19/22 First Order
123 abc5 3/22/22 Third Order

Any thoughts?

Solution

  • This should return the numeric sequence:

    Select 
    [customer id],
    [order #],
    [date],
        (Select Count(*) 
        From YourTable As T 
        Where T.[customer id] = YourTable.[customer id] 
        And T.[date] <= YourTable.[date]) As [Order Sequence No.]
From
    YourTable
Order By
    [date],
    [customer id]

    You can browse for a lot of solutions to spell out the numbers to English.