should I use more than one query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a study and would like to count a number of different occurrence in diffferent fields. For example, I would like to count the number of cases and controls in the classification field, the number of eligibles and ineligibles in another field and the participant progress as designated by the completion of a home visit, a first visit and a second visit..

Can I make one query that counts all of this information (that hopefully is easy to turn into a report)

Or should I develop many different queries and include all the count fields generated in the report I want to generate
(I have tried this. When I am ready to generate a report and include all the fields from all the queries that I have generated, I receive an error message indicating that the report cannot be generated because I am including information from a table and query of the same table

If you could explain if I should make one or multiple queries and sketch how I should go about doing it?
 
Dear Mag:

Are you then thinking of a one row query with various unrelated
statistics from the database?

You can use a subquery to get each of the statistics:

SELECT
(SELECT COUNT(*) FROM
(SELECT DISTINCT Classification FROM Table1)) AS CasesInTable1,
(SELECT COUNT(*) FROM Table2 WHERE AnotherField = "eligible") AS
Table2Eligibles
FROM AnyOldTable

You can query these one at a time, using a column to describe the
result and another column to contain the numeric result:

SELECT "Cases In Table 1" AS ValueDescription,
(COUNT(*) FROM
(SELECT DISTINCT Classification FROM Table1)) AS Value
FROM AnyOldTable
UNION ALL
SELECT "Table 2 Eligibles" AS ValueDescription,
(SELECT COUNT(*) FROM Table2 WHERE AnotherField = "eligible")
FROM AnyOldTable

This creates a 2 column result.

The report you can build around these two examples would be very
different in organization. The latter would be more flexible in
adding new information - you wouldn't have to modify the report when
you add new things to display.

These are just some rough, untested ways of approaching the problem.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top