Access query Distinct Row Count

[armenperez]

I have a query that I want the count to be incremental for each number with the same date

Entry Date Number Count
01/15/2016 30519 1
01/15/2016 30519 2
01/15/2016 30519 3
01/15/2016 30519 4
01/16/2016 12345 1
01/16/2016 12345 2
01/16/2016 12345 3
01/16/2016 12345 4
01/15/2016 34569 1
01/15/2016 34569 2

[Rabbit]

You're going to need another field in there that's distinct by row or by the natural key. It's not possible to do otherwise aside from using VBA to do it row by row which can be extremely slow if you have a large dataset.

[mbizup]

You probably already have a primary key field in your table, since Access practically begs you to include one when you create a table. If that PK field is autonumbering, you can use it to definitively determine the order of the records. Alternatively, if your Entry Date field includes a time stamp, then that field could be used for the same purpose. The timestamp could be split into a date part for grouping while using the time part to determine order. Basically, you need to have some way to determine the order of records where the Number and Entry Date fields are the same.

If you can do that, then your record number can be determined by a SELECT COUNT(X) subquery using criteria that includes records where the PK (or Time Stamp) field is LESS than that in the current record, and the Number field and Date are equal to that in the current record.

[armenperez]

I have added an id# that is autonumber.

Entry Date Number ID# Count
1/15/2016 30519 1 1
1/15/2016 30519 2 2
1/15/2016 30519 3 3
1/15/2016 30519 4 4
1/16/2016 12345 5 1
1/16/2016 12345 7 2
1/16/2016 12345 8 3
1/16/2016 12345 9 4
1/15/2016 34569 5 1
1/15/2016 34569 6 2

[Rabbit]

You can use a ranking query to accomplish what you want.

https://bytes.com/topic/access/insights/954764-ranking-queries-ms-access-sql