Serial No for Unique Value

[K]

Hi all, I have Select Query "Q_Data" linked to Table "T_Data". When
I run Query I get values like see below

Col1
AAA
AAA
AAA
BBB
BBB
CCC
CCC
CCC
CCC

I want some formula or way so when I run Query then I should get
result in next column like see below.

Col1 Col2
AAA 1
AAA 2
AAA 3
BBB 1
BBB 2
CCC 1
CCC 2
CCC 3
CCC 4

Please can any friend can help

 

[John Spencer]

To get that result you need to have another value (column) in T_Data that will
order the items within the group or a primary key that you can use that orders
the items over all.

If you search for RANKING query, you can find some examples.

Your other choice would involve writing a custom VBA function.

If you are doing this to assign the number in a report, it is fairly simple to
do so there and ignore doing it in the query.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County