Counting number of certain entries

[Holly Cetron]

I have a database with "Disease" as a field. Entries
include things like Salmonella, E.coli, Hepatitis A...
I would like to count the total number of each entry in
the field (total number of Salmonellas, E.colis, and
Hepatitis A's) and get the total number of each and list
the top 10 most frequent. I'd also like to limit the
query to a certain date range. Can someone tell me how to
do this in query design?
Thanks, Holly

 

[John Vinson]

I have a database with "Disease" as a field. Entries
include things like Salmonella, E.coli, Hepatitis A...
I would like to count the total number of each entry in
the field (total number of Salmonellas, E.colis, and
Hepatitis A's) and get the total number of each and list
the top 10 most frequent. I'd also like to limit the
query to a certain date range. Can someone tell me how to
do this in query design?
Thanks, Holly

A Totals query will do this for you.

Create a Query based on the table; select the Disease field *twice*
and the date field. Put a criterion of

BETWEEN [Enter start date:] AND [Enter end date:]

on the criteria line under the date field. Change the query to a
Totals query by clicking the Greek Sigma icon (like a sideways M).

Change the default Group By field under the date field to Where, and
change one of the two Group By's under the Disease field to Count.
Sort this field in descending order.

Finally, view the Query's Properties and set the Top Values property
to 10.

 

[John Spencer (MVP)]

The SQL would look something like:

SELECT Top 10 Disease, Count(Disease) as CountofDisease
FROM TABLE
WHERE SomeDateField Between #1/1/03# and #1/1/04#
GROUP BY Disease
ORDER BY Count(Disease) Desc

In the Query grid, add the following fields
Disease (First time)
Disease (Second Time)
SomeDateField

Select View: Totals from the Menu

In the new Total row, select as follows
Disease (First time) Group By
Disease (Second Time) Count
SomeDateField WHERE

Enter "Between #1/1/03# and #1/1/04#" in the criteria under SomeDateField.

Select "Descending" under the second Disease field

Right Click on the grey area of the query grid and select "Properties"
In the properties, type "10" in the Top Values property.

That ***should*** do it.