Apply Sequential Labels To Records in Access-CodePudding

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?

CodePudding user response：

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.