Increment Duplicate Fields With Counter

[bigmerf]

I have a query of data for which contains an InvoiceID field. There are several instances where the InvoiceID field will be duplicated. In these instances, I want to create a custom column that will increment starting with 1 then 2 then 3 ans so on.

Is there a function or anything that I can create within the query to have this column increment when in fact it does find a duplicate.

In Excel, can write an "easy" IF statement to compare the value of the field to the field above to see if it matches then add +1 to the increment field. I'd like to perform the same thing in Access if at all possible.

Any suggestions?

Thanks!

 

[dhookom]

There is no "the field above" in Access. You can create the sequence number using DCount() or a subquery in your SQL statement. It all depends on the table and fields and order you want to use for the sequence.

You might want to search on "Query Rank" here and elsewhere.

Duane
Hook'D on Access
MS Access MVP