Count Query Help

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

I have a table that contains various fields. One of these fields is
[AreaOfWork], who's value is one of a possible 10 from a lookup table.

For each of these 10 values, I would like to count how many records
have the particular value.

I'd like to do this without having to make 10 queries - is there an
easy way?

Many thanks - David
 
SQL statement would look like

SELECT AreaOfWork, Count(AreaOfWork) as UseCount
FROM [Your Table Name]
GROUP BY AreaOfWork

In the query grid
--Select your table
-- Drag AreaOfWork to the list of fields twice
-- Select View: Totals from the menu
-- In the Total line in the grid change one of the Group By to Count

Run the query.
 
Looks like you know how to write a query. You just dont want to
write a query for each AreaOfWork.

Ex. QueryOne - Criteria for AreaOfWork is Teacher
QueryTwo - Criteria for AreaOfWork is Doctor

Couple of Solution:
1. You can use a parameter in your query to prompt the user for the area of
work

2. If you are designing a form, you can pass areaofwork into the query.
Ex. On your form is a txtbox, with the AreaofWork. You can set the
criteria
of AreaofWork to that txt box. The criteria would be something like
this
Forms![yourFormName]![AreaofWork]. You can use the expression builder
to locate the txtbox.

Keep in mind:
the form has to be open for the query to work, because the query is
looking
for a criteria that is on that form.

The setup of count would remain the same. Now you won't need A query
for each type of areaofwork.
 
Back
Top