count records with like fields by query.

[Guest]

I can sort records by name within field but, how can I use Access to count
the number of times the field record is repeated? For example: A field has
these records blue, blue, blue, blue, red and red. So my count total will
be 4 blue and 2 red. I have hundreds of records with the same color and
hundreds more of other colors that I would like to total for each color.
Then I can easily do a chart with the totals. Any suggestions on how to
query? Or, can this only be done with a report?

 

[Guest]

Try this ---
SELECT YourTable.Color, Count(YourTable.Color) AS CountOfColor
FROM YourTable
GROUP BY YourTable.Color;

 

[Guest]

Hi Karl,

Thank you. It worked. I was able to sort the query in descending order by
quantity. But, when I create a chart the quanities are now scattered. Why
did it not keep the same sort order? I want a bar chart showing a gradual
decend from largest quantity to fewest quantity.

 

[Guest]

Try opening the graph in design view and edit the Row Source to add to the
select statement the same ORDER BY statement as in your query.

 

[Guest]

Karl,

That did not work. I'll keep trying. Any other suggestions?

 

[dhabtem1]

maybe try going to your original query:

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

this is still producing scattered results?