count records with like fields by query.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

FL - 26 Oct 2007 22:33 GMT

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?

Signature

Reply to this Message

KARL DEWEY - 26 Oct 2007 22:39 GMT

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

Signature

KARL DEWEY
Build a little - Test a little

> 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
[quoted text clipped - 3 lines]
> 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?

Reply to this Message

FL - 26 Oct 2007 23:06 GMT

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.

Signature

> Try this ---
> SELECT YourTable.Color, Count(YourTable.Color) AS CountOfColor
[quoted text clipped - 8 lines]
> > 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?

Reply to this Message

KARL DEWEY - 26 Oct 2007 23:36 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> Hi Karl,
>
[quoted text clipped - 15 lines]
> > > 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?

Reply to this Message

FL - 27 Oct 2007 00:21 GMT

Karl,

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

Signature

> 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.
[quoted text clipped - 18 lines]
> > > > 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?

Reply to this Message

dhabtem1 - 01 Nov 2007 17:05 GMT

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?

Reply to this Message