Duplicates numbered

[Céline Brien]

Hi everybody,
I created a query to find duplicates.
I would like to number the duplicates.
red 1
red 2
red 3
green 1
green 2
Blue 1
Blue 2
Blue 3
Either in the same query or in another query based on this one.
Many thanks,
Céline

 

[Duane Hookom]

You can't number records in a query without having a field in the
table/query that can be used to "rank" the records. Could you provide more
information on what your records look like and how you want to rank them?

 

[Céline Brien]

Hi everybody,
Hi Duane,
Thank you very much for your answer.
You are absolutely right.
As a matter of fact, I created the table Color with the result of the query
to find duplicates.
My question reformulated is :
In a query using the table Color, containing the field Colors, I would like
to number the duplicates in the field Number
Colors Number
red 1
red 2
red 3
green 1
green 2
Blue 1
Blue 2
Blue 3
Many thanks for your help,
Céline

 

[Céline Brien]

Hi everybody,
I forgot to had that each color has a unique number :
ID Colors Number
1 red 1
2 red 2
3 red 3
4 green 1
5 green 2
6 Blue 1
7 Blue 2
8 Blue 3
Thanks,
Céline

 

[Duane Hookom]

Try:
SELECT ID, Colors, (SELECT COUNT(*) from Color C WHERE C.Colors =
Color.Colors AND C.ID <= Color.ID) as Number
FROM Color;

 

[Céline Brien]

Hi everybody,
Hi Duane,
Thank you so much for your help.
It is working perfectly ! You've made my day !
SQL is still a foreign languague for me, I am very impress.
Many thanks again,
Céline

 

[Céline Brien]

A little detail, I had to use Number with an s because Number is reserved.