count records with like fields by query.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Try this ---
SELECT YourTable.Color, Count(YourTable.Color) AS CountOfColor
FROM YourTable
GROUP BY YourTable.Color;
 
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.
 
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.
 
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?
 
Back
Top