Duplicates numbered

C

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
 
D

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

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

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
 
D

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top