Re: Duplicates numbered

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


--
Duane Hookom
MS Access MVP


"Céline Brien" <celinebrien@xxxxxxxxxxxxxxxxx> wrote in message
news:ORdAPjh7GHA.5020@xxxxxxxxxxxxxxxxxxxxxxx
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

"Céline Brien" <celinebrien@xxxxxxxxxxxxxxxxx> a écrit dans le message de
news: Og%23Ykeh7GHA.4500@xxxxxxxxxxxxxxxxxxxxxxx
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


"Duane Hookom" <DuaneAtNoSpanHookomDotNet> a écrit dans le message de
news: %23F4y2tg7GHA.2120@xxxxxxxxxxxxxxxxxxxxxxx
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?

--
Duane Hookom
MS Access MVP

"Céline Brien" <celinebrien@xxxxxxxxxxxxxxxxx> wrote in message
news:%23%23WKNmg7GHA.4064@xxxxxxxxxxxxxxxxxxxxxxx
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









.

Relevant Pages

  • Re: Beginner query help please
    ... MS Access MVP ... Duane Hookom wrote: ... I've noticed that if you select all the query output (in this ... Controls in most query solutions would not be ignored. ...
    (microsoft.public.access.queries)
  • Re: Beginner query help please
    ... MS Access MVP ... I've noticed that if you select all the query output (in this case ... Duane Hookom wrote: ... Controls in most query solutions would not be ignored. ...
    (microsoft.public.access.queries)
  • Re: Tricky query question
    ... Microsoft Access MVP ... "Duane Hookom" wrote: ... dynamic monthly crosstab report solution found at ... July data minus June data) in a crosstab query? ...
    (microsoft.public.access.queries)
  • Re: Formula will not work with mail merge
    ... There is code that does this in the Query By Form ... > How do I push the records to a separate Word Merge format file? ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.queries)
  • Re: Crosstab Report
    ... MS Access MVP ... I try to write the report my headings/totals crash. ... "Duane Hookom" wrote: ... some of the information you posted to the query and found it very ...
    (microsoft.public.access.reports)