Duplicates numbered but with a new condition

[Céline Brien]

Hello all,
From the table Color,
ID Colors
1 red
2 red
3 red
4 green
5 green
6 Blue
7 Blue
8 Blue
Using a query kindly propose by Duane Hookom (see below), the duplicates are
numbered that way :
ID Colors Numbers
1 red 1
2 red 2
3 red 3
4 green 1
5 green 2
6 Blue 1
7 Blue 2
8 Blue 3
Now, from the same table called Color, but using a new field call YearMonth,
is it possible to create a query to number the duplicates that way :
ID Colors YearMonth NewNumbers
1 red 05-12 1
2 red 05-12 1
3 red 06-03 2
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 2
8 Blue 05-04 3
The first duplicate is always a number 1.
The second, third, etc. are also numbered 1, if the YearMonth is the same as
the first. Exemple : the red
If the YearMonth are the same for any orther duplicates than the first, the
duplicates are numbered in a sequence. Exemple : the blue
There is definitely no limit to the needs of the people ;-)
Thank you so much for your help,
Céline

 

[Marshall Barton]

From the table Color,
ID Colors
1 red
2 red
3 red
4 green
5 green
6 Blue
7 Blue
8 Blue
Using a query kindly propose by Duane Hookom (see below), the duplicates are
numbered that way :
ID Colors Numbers
1 red 1
2 red 2
3 red 3
4 green 1
5 green 2
6 Blue 1
7 Blue 2
8 Blue 3
Now, from the same table called Color, but using a new field call YearMonth,
is it possible to create a query to number the duplicates that way :
ID Colors YearMonth NewNumbers
1 red 05-12 1
2 red 05-12 1
3 red 06-03 2
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 2
8 Blue 05-04 3
The first duplicate is always a number 1.
The second, third, etc. are also numbered 1, if the YearMonth is the same as
the first. Exemple : the red
If the YearMonth are the same for any orther duplicates than the first, the
duplicates are numbered in a sequence. Exemple : the blue
There is definitely no limit to the needs of the people ;-)
Thank you so much for your help,
Céline
----------------------------------------------------------------------------------------
Code of the first query :
SELECT ID, Colors, (SELECT COUNT(*) from Color C WHERE C.Colors =
Color.Colors AND C.ID <= Color.ID) AS Numbers
FROM Color;

Use the YearMonth field instead of the ID field:

SELECT ID, Colors,
(SELECT COUNT(*) from Color C
WHERE C.Colors = Color.Colors
AND C.YearMonth <=Color.YearMonth) AS Numbers
FROM Color

Note that the result will be:

1 red 05-12 1
2 red 05-12 1
3 red 06-03 3
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 2
8 Blue 05-04 2

 

[Céline Brien]

Hello all,
Hello Marshall,
Many thanks for your answer.
The result is :
1 red 05-12 2
2 red 05-12 2
3 red 06-03 3
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 3
8 Blue 05-04 3
and I need some thing like this :
1 red 05-12 1
2 red 05-12 1
3 red 06-03 2
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 2
8 Blue 05-04 2
I need to identify the oldest one, or oneS if they where in the same month.
I don't mind the :
1
1
3
or
1
1
2
Any other suggestion ??
Thanks again,
Céline

 

[Marshall Barton]

Ahh sorry, that should have been:

SELECT ID, Colors,
(SELECT COUNT(*) from Color C
WHERE C.Colors = Color.Colors
AND C.YearMonth < Color.YearMonth) + 1 AS Numbers
FROM Color

 

[Céline Brien]

Hi everybody,
Hi Marshall,
Thank you so much for your answer.
You don't have to by sorry, your solution works perfectly !
I am so impress by the power of SQL.
After your first answer I tried a few things, but could'nt get the right
solution.
Again many many thanks !
Céline

Ahh sorry, that should have been:

SELECT ID, Colors,
(SELECT COUNT(*) from Color C
WHERE C.Colors = Color.Colors
AND C.YearMonth < Color.YearMonth) + 1 AS Numbers
FROM Color
--
Marsh
MVP [MS Access]

The result is :
1 red 05-12 2
2 red 05-12 2
3 red 06-03 3
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 3
8 Blue 05-04 3
and I need some thing like this :
1 red 05-12 1
2 red 05-12 1
3 red 06-03 2
4 green 05-10 1
5 green 06-02 2
6 Blue 04-12 1
7 Blue 05-04 2
8 Blue 05-04 2
I need to identify the oldest one, or oneS if they where in the same
month.
I don't mind the :
1
1
3
or
1
1
2
Any other suggestion ??
Thanks again,
Céline


"Marshall Barton" a écrit