Counts in Querys (and reports)

[Guest]

I'm trying to create a report (based on a query) with multiple layers of
counts (i.e. total states, total counties in each state, total cities in each
county).

When I try to create one query w/ more than one count, each count I enter
(regardless of the field) ends up being the count for the lowest common
denominator of the "group by" fields. In my example above, I would get 2
cout columns with the cout of cities in both in spite of the fact that one
was labeled count counties.

I also tried to create a report based on 3 different querys (each one with
the unique count I'm looking for) and I can't get past the first screen. The
error I'm getting is...
"YOU HAVE CHOSEN FIELDS FROM RECORD SOURCES WHICH THE WIZARD CAN'T CONNECT.
YOU MAY HAVE CHOSEN FIELDS FROM A TABLE AND FROM A QUERY BASED ON THAT TABLE.
IF SO, TRY CHOOSING FIELDS FROM ONLY THE TABLE OR ONLY THE QUERY."
I've tried making sense of the erry, but I can't. Is it possilble to create
a query with multiple layered counts? Thanks in advance for any advice
anyone can share.

Dj

 

[Guest]

Do your counts at the report level. First, you are seeing the effects of
multiple counts in a query, and Second, there is a huge performance
difference. Any time you are doing a query for a report and you have any
Domain Aggregate functions involved, do them in the report. I once
inheritied such a beast. The report took about an hour to run. I moved all
the functions out of the query and into the report and in ran in about 3
minutes. (No exageration).

 

[Guest]

I'm sorry I have to ask how do you do that. While I'm at it, can you
reccommend a good reference book? I'm using ACCESS 2002: THE COMPLETE
REFERENCE and there's just too much missing. I'm teaching myself access,
more or less, through trial and error because the book lacks the detail that
I need. If this book tells me how to get counts at the report level, I
missed it; but I'll look again. Thanks for your help Klatuu.

 

[Guest]

In the control source of a text box on the report where you want to count,
use the DCount function just like you would in a query.

My favorite reference is a pair of books published by Sybex it is by Litwn,
Getz, and Gunderloy. They are Access 2002 Enterprise Developer's Handbook
and Access 2002 Desktop Developer's Handbook.