JustinP said:
I need to put about 20 record counts in a report. As an example, count
of number of properties marked private, count of number of properties
marked private and redundant, count of number of properties marked
government, etc, etc.
What is the best way to go about this? i.e. by avoiding writing 20
queries then putting the results into one table...
You don't describe the structure of the relevant table(s) so all I can
give are some examples. Let's suppose you have a table tblProperties,
with boolean fields to store the attributes you've mentions, like this:
Table: tblProperties
-------------------------
Field: PropertyID (autonumber, primary key)
Field: PropertyDescription (text)
Field: IsPrivate (boolean)
Field: IsRedundant (boolean)
Field: IsGovernment (boolean)
Now, if all you want is to produce a summary report, you could base it
on a totals query with SQL like the following:
SELECT
Count(*) AS TotalProperties,
Abs(Sum(IsPrivate)) AS PrivateCount,
Abs(Sum(IsPrivate And IsRedundant))
AS PrivateAndRedundantCount,
Abs(Sum(IsGovernment)) AS GovernmentCount
FROM
tblProperties;
You'd bind your summary report's text boxes to the calculated fields
TotalProperties, PrivateCount, PrivateAndRedundantCount,
GovernmentCount, and so on.
On the other hand, you may be wanting this summary information to appear
in the Report Footer section of a detail report that shows all
properties. In that case, you would base the report on the table,
tblProperties (or a simple, non-totals query of that table), but yopu
could add calculated text boxes to the report footer section with these
ControlSource expressions:
=Count(*)
=Abs(Sum(IsPrivate))
=Abs(Sum(IsPrivate And IsRedundant))
=Abs(Sum(IsGovernment))
Does that make it clear how to go about it?
