Query Increment Count

[bistro7]

I have a query with dates, where I want to incrementally get each occurrence of the same date. Here is an example of what I am trying to do: Any help would be appreciated!

Date Count
1/5/03 1
1/5/03 2
2/10/03 1
2/27/03 1
3/9/03 1
3/9/03 2
3/9/03 3

 

[Quehay]

This is a simple SQL aggregate query using COUNT function.
The thing to watch for is that dates always have a time component in them--it's stored as a double value by Access. 7 July 2003 14:30 is not the same to Access as 7 July 2003 12:00 am (the default time value is midnight if not explicitly set by the input)

Look in the MS Knowledge Base:

Q210276 ACC2000: Storing, Calculating, and Comparing Date/Time Data


SELECT
Count(Orders.OrderDate) AS CountOfOrderDate
FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Orders.OrderDate;

 

[bistro7]

Quehay, thanks for the response, but when I run the query I am getting the total count for the number of occurances of the same date. e.g. For the below example I get a total of 3 instead of an increment count of 1,2,3. Any ideas?

Date
9/5/03
9/5/03
9/5/03

 

[Quehay]

Bistro,

You're asking for set logic to produce a sequential ranking of your results. This is outside of set results--it's asking for a group of rows based on criteria and then asking for a procedural review of the sequential results for each date.

You CAN do this with an access report by grouping by date and having a text box with an increment formula for its source (I forget the exact way to do it.) Try this Microsoft Knowledge Base article:

Microsoft Knowledge Base Article - 98790