Counting a count

[Guest]

How do I count a count??

This is what im trying to do:

I have a database with a table named tblMain and has incorporated everything
within that table but contains info from about 8 other tables. I have not
doesigned this database and it appears to have a very poor structure...anyway

There are three fields involved in this process...'Request' (either
'dispensation', 'waiver' or 'dispensation and waiver'), 'Department' (which
consists of 7 other drop down menu options) and 'RequestStatus' which
contains the status of each request by another drop down menu. I am looking
to count the number of dispensations and waivers in each department and then
count the number at each of the different stages. For example

If department 1 had 4 requests, 2 for disps, 1 for a waiver and 1 for both
and 1 disp request was at status 1, the other at status 2, the waiver at
status 3 and the request for both at status 4. Could I write a query that
displays all that.

I have wrote a query that counts the number of requests in each status for
the department but does not seperate between disps and waivers. I have also
wrote a query that counts disps for a department but I would like a
combination of the 2.

I hope this is clearer than I think it sounds

Thanks

 

[J]

RJW:

I admit I don't fully understand your explanation. Here are some tips
that might solve your problems:

1) You can nest queries. That means you can call a query as a
recordsource just like a table. You can use this technique to have
one querie that calls two other queries. Then you can do a "Count" on
a field that already is the count of something else. You can also do
a "Sum" on a field that is already the count of something else, which
would effectively add count fields.

2) You can do union queries. These are queries that append
eachother. You have to edit these in SQL mode, however and the syntax
is "SELECT ? FROM ? WHERE ? UNION SELECT ? FROM ? WHERE ?". They must
have the same fields.

3) You can do pivot table views on a query, which would let you
establish a 'data hierarchy' with totals. I THINK THIS IS WHAT YOU
ARE LOOKING FOR. To do this, create a query that selects all the data
from your table without counting or aggregating anything. Then right
click and choose "Pivot Table View" and set up all your COUNT
functions etc there.
Put the Request, Department, and RequestStatus fields all as rows.
Then put your ID field (or whatever field gets counted) in the data
part. Right click that field and set its autototal or autocalc or
whatever to "Count". You'll have to play around with getting
subtotals and subcounts.

Best of luck!
~J