More than 1 query in a report.

[Guest]

I've asked this question before and I've seen others ask
it but I've never
seen an answer that made sense to me. Not because the
response was wrong or
poorly written but because of my very limited experience
in Access. I'm
going to ask my question in a different way. If someone
could walk me
through this (click this, now type that, etc.) or post a
sample somewhere
I'd be very, very grateful.

Let's say I have a table that shows name and state of
customers.

Joe Smith TX
Bob Jones NY
Andy Kaufman NY
Mary Queen of Scots TN
Joe Doe NY
Joe Grant NY

I need a report that shows me

1.The total number of customers in New York
2. The total number of customers with the first name of
Joe

So it would look like this:
NY Customers: 4
Customers named Joe: 3

How do I do it? I'm currently using a query to give me a
listing of all
customers named Joe and another query to give me all
customers from NY. I
can't get both queries to show up on the same report. This
seems to me like
it woud be a basic feature of Access so I must be an
idiot. Please help!

 

[Jeff Boyce]

Queries aren't "in" reports. Reports can be based on either tables or
queries. Can you create two reports, one each for each query?

Consider a main report/ sub-report design, in which you'll embed your two
(sub-) reports on a main report.

 

[John Evans]

The answer is in Jeff's response, "query." Create a query
that gives you the desired response. If your response to
your "Joe" and "NY" are in textboxes, you can use the
Domain Aggregate functions. But, in place of the table
name, use the query name you created and saved that gives
you the desired response. This function is placed in the
textboxes ControlSource. An example for the NY would be;

=DCount("state field name","query name","state field
name='NY'")

The "Joe" would be similar in structure.

Check Access Help for more info on domain aggregates.
Hope this helps.
*** John

 

[Jeff Boyce]

John

Just a personal preference on my part, so take it with a grain...

If I can avoid using the D*() functions, I do. They tend to be resource and
time intensive. Whenever possible, I'll use set operations (i.e., queries).
And I do still find occasion when a D*() is faster (for me to build).