Counter Question

[Rose Lentz]

I have a query that strips a prefix off the value stored in one field (field
1) into a new field (field 2). This results in a number of duplicate values
in field 2 of my table. Example: Separate records with ABC123 and DEF123
stored in field 1 results in two records with the base value of 123 in field
2.

I would like to add another column to my query that will append suffix to
the base value making each value unique. The suffix would consist of a
hyphen and a sequential number; i.e., ABC123 in field 1 ultimately becomes
123-1 and DEF123 becomes 123-2.

The sequential number part of the suffix would reset to 1 every time the
base value changes (i.e., from 123 to another number). Can anyone help me
with this?

 

[Michel Walsh]

Hi,



SELECT a.prefix, a.theNumber, COUNT(*) As suffix
FROM myTable As a INNER JOIN myTable As b
ON a.theNumber=b.theNumber
AND a.prefix <= b.prefix
GROUP BY a.prefix, a.theNumber



assuming that the couple (prefix theNumber) has no duplicate occurrence



Hoping it may help,
Vanderghast, Access MVP