Count occurances while keeping duplicates

[Mr Banan Qry]

Hi!
I'm trying to create ONE query which would fetch me the number of occurences on a particular value in a field while keeping duplicates.
For instance let's say i've got the following table
Color......Value
Red.........5
Red.........8
Orange....1


Then i would like to have the following result from my query
Color.........Value.......CountOfColor
Red............5..............2
Red............8..............2
Orange.......1..............1

I know i could achieve this by creating a view first. For instance by saving this expression as qryCountOfColors

SELECT tblColors.Color, Count(tblColors.Color) AS CountOfColor
FROM tblColors
GROUP BY tblColors.Color;

And then make the following query

SELECT tblColors.Color, tblColors.Value, qryCountOfColors.CountOfColor
FROM tblColors INNER JOIN qryCountOfColors ON tblColors.Color = qryCountOfColors.Color

However I'm woundering wheter or not I could achieve it without creating the qryCountOfColors.
I'm :banghead: myself into a mental meltdown . I've been playing with the idea of creating of creating a subquery to achieve this but im unable to get to work

SELECT tblColors.Color As BANANA, tblColors.Value, (SELECT  Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color HAVING (((tblColors.Color)=BANANA))) AS Expr1
FROM tblColors;

I would be extremely grateful if someone could help along and explain wheter or not it's posssible and if is how to go about it. It would allow me to go back to normal sleeping hours

 

[bob fitz]

Try this as the SQL of your query:
SELECT tblColors.Color, tblColors.Value, DCount("Color","tblColors","Color ='" & [Color] & "'") AS CountOfValue
FROM tblColors
GROUP BY tblColors.Color, tblColors.Value;

 

[Mr Banan Qry]

Thanks!

It does work like a charm but it feels like cheating to use a function almost as using VBA by which it would be possible to retrieve whatever value you'd want

I eventually got my head around it and managed to produced a pure SQL expression which provides

SELECT
tblColors.Color,
tblColors2.CountedColors
FROM
tblColors,
(SELECT Color, COUNT(Color) AS CountedColors FROM tblColors GROUP BY Color) AS tblColors2
WHERE
tblColors.Color = tblColors2.Color