Count up by date for each customerID

[Cameron]

I am trying to count in order of purchase for each customer using the
CustomerID and Purchase Date. However there are some customers that purchase
more than once in the same day and I don't have a timestamp. So the count
looks like the following:

CustomerID PurchaseDate Rank
100 1/1/07 1
200 2/1/07 1
200 3/1/07 2
200 3/1/07 2
200 8/1/07 4

I have been using a query that looks like this one:

SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

-----
It doesn't matter to me what order the same date purchases get, and I do
have an order number that is unique but I can't count on the fact that the
order number is actually in an order sequence that would match the date of
the order. Meaning two dates can be the same but the earliest date may have
a greater order number.

This is so simple to do for me in excel, but now becoming a nightmare in
sql/access.

 

[Sean Timmons]

so you want to group by I assume?

SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1

GROUP BY customerID, PurchaseDate

WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

ought to group those 3/1 orders together

I am trying to count in order of purchase for each customer using the
CustomerID and Purchase Date. However there are some customers that purchase
more than once in the same day and I don't have a timestamp. So the count
looks like the following:

CustomerID PurchaseDate Rank
100 1/1/07 1
200 2/1/07 1
200 3/1/07 2
200 3/1/07 2
200 8/1/07 4

I have been using a query that looks like this one:

SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

-----
It doesn't matter to me what order the same date purchases get, and I do
have an order number that is unique but I can't count on the fact that the
order number is actually in an order sequence that would match the date of
the order. Meaning two dates can be the same but the earliest date may have
a greater order number.

This is so simple to do for me in excel, but now becoming a nightmare in
sql/access.

 

[John Spencer]

I think this may work for you. I can't test it.

SELECT customerID, PurchaseDate
, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID
AND T1.PurchaseDate <= T.PurchaseDate
AND T1.[OrderNumber] <= T.[OrderNumber]) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

so you want to group by I assume?

SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1

GROUP BY customerID, PurchaseDate

WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

ought to group those 3/1 orders together

I am trying to count in order of purchase for each customer using the
CustomerID and Purchase Date. However there are some customers that purchase
more than once in the same day and I don't have a timestamp. So the count
looks like the following:

CustomerID PurchaseDate Rank
100 1/1/07 1
200 2/1/07 1
200 3/1/07 2
200 3/1/07 2
200 8/1/07 4

I have been using a query that looks like this one:

SELECT customerID, PurchaseDate, (SELECT COUNT(*)
FROM [Cameron] T1
WHERE T1.customerID = T.customerID AND T1.PurchaseDate <=
T.PurchaseDate) AS Rank
FROM [Cameron] AS T
ORDER BY customerID, PurchaseDate;

-----
It doesn't matter to me what order the same date purchases get, and I do
have an order number that is unique but I can't count on the fact that the
order number is actually in an order sequence that would match the date of
the order. Meaning two dates can be the same but the earliest date may have
a greater order number.

This is so simple to do for me in excel, but now becoming a nightmare in
sql/access.