Duplicate Count

[FBxiii]

Hi. This seems like an impossible question, but here goes!

I have a table that contains a number of duplicates. I have got a
duplicates query to identify them but I need to put a count against each
duplicated row. The count needs to reset at every new occurance of a
duplicate.

e.g.

Count Reference
1 12345
2 12345
3 12345
1 67890
2 67890
1 99999

Any ideas? Ive tried a few things but just cant crack it!!

 

[Bob Barrows]

Hi. This seems like an impossible question, but here goes!

I have a table that contains a number of duplicates. I have got a
duplicates query to identify them but I need to put a count against
each duplicated row. The count needs to reset at every new occurance
of a duplicate.

e.g.

Count Reference
1 12345
2 12345
3 12345
1 67890
2 67890
1 99999

Any ideas? Ive tried a few things but just cant crack it!!

This could easily be done in a report. to do it in a query:

The only way to do this is to use a subquery to count the Reference
records that are "less than" or "lower than" the Reference number in
the current record. To do that, you will need a field to put those
Refence numbers in order. Is there an autonumber field in this table
that can be used? If so, the query would look like this:

select
(select count(*) from yourtable as i where i.Reference=o.Reference and
i.autonumber<=o.autonumber) as Runningcount,
Reference
FROM yourtable as o

 

[FBxiii]

Im not 100% sure how it works, but it does!

Cheers,
Steve.