count Duplicate

[JustinC]

Hi all,

I have a sample table with duplicate.

ID. Name Items
123456 James Book
123456 James Pencil
876543 Elan Ruler
123456 James Pen
876543 Elan Paper
765432 Gerald Card

How do I add a column to count duplicate sequentially?

Output:

ID. Name Items Count
123456 James Book 0
123456 James Pencil 1
123456 James Pen 2
876543 Elan Ruler 0
876543 Elan Paper 1
765432 Gerald Card 0

Thank you!

[pbaldy]

It would be simple on a report, using the running sum property of a textbox (after grouping on the person). In a query you'd need to use DCount() or a subquery, either of which could really slow things down.

[JustinC]

Hi Phaldy,
Can you show me an example of DCount() or subquery to accomplish this?

Thanks!

[pbaldy]

Along the lines of:

DCount("*", "TableName", "ID = " & ID & " AND Items < '" & Items & "'")