how to count specific values only not total count

  • Thread starter Thread starter steven
  • Start date Start date
S

steven

I am using Access 2000 - I have one column of numbers (probably 1000 numbers
in total) ranging from 1 to 9 - Does anyone know how I can count the times
that each specific number appears - for instance - count the total number of
ones, count the total number of two's, three's, four's etc.

I have tried this in a query and have not yet been successful - does it need
to go only in a report?

Thank you very much - Steven
 
I am using Access 2000 - I have one column of numbers (probably 1000 numbers
in total) ranging from 1 to 9 - Does anyone know how I can count the times
that each specific number appears - for instance - count the total numberof
ones, count the total number of two's, three's, four's etc.

I have tried this in a query and have not yet been successful - does it need
to go only in a report?

Thank you very much - Steven
Click to expand...

use a totals query.

SELECT Value, Count(Value)
FROM MyTable
ORDER BY Value
GROUP BY Value;
 
You can do it in a query. Put the field that contains the numbers into a
query twice.
Click on the Sigma (totals) button on the query toolbar. You will see total
row appear in your query grid with gruoup by set as default. Leave the first
group by alone but change the 2nd one to Count.
 
Ensure that your table has at least one more field (besides the number, and
you can create one by simply adding an AutoNumber field to the table), then
do this:

Create a query, adding the number field and any other field to it. Click the
Sum button (Greek Sigma, somewhat like a capital E). Then change the Total to
GroupBy for the number field and Count for the other one. The Count result
will be the # of occurences of each number.

If your query has only the one field, you can do the same thing, but after
grouping by the number field, add another field: RecordCount: Count(*) with
Total as Expression (Access will not let you add the * summary by
double-clicking it)
 
Back
Top