queries on report

[Suze]

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!

 

[John Vinson]

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!

The two queries is a perfectly reasonable approach. Note that you can
display two (or ten) queries on the same report by using Subreports,
one subreport based on each query; it is *NOT* necessary to create one
magical do-everything query!

That said, you *can* get the desired results in a single query. It
seems a very strange thing to want to do, but you can create a Query
based on your table; don't select any fields from the table at all.
Instead put two calculated fields in the query:

FromNY: IIF([State] = "NY", 1, 0)
NamedJoe: IIF([FirstName] = "Joe", 1, 0)

This query will show two columns, mostly zeros, with 1 in the first
column for any record from NY, 1 in the second column for each Joe.

Change the Query to a Totals query by clicking the Greek Sigma icon.
Change the default Group By field on the totals row to Sum. Run the
query; you'll see two fields in one record:

SumOfFromNY: <a count of the NY records>
SumOfNamedJoe: <a count of Joes>

 

[Guest]

Thanks. How do I create subreports? If these are just
regular reports that happen to get called into another
report I don't see where the data for a report can be
another report. ???

-----Original Message-----

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!

The two queries is a perfectly reasonable approach. Note that you can
display two (or ten) queries on the same report by using Subreports,
one subreport based on each query; it is *NOT* necessary to create one
magical do-everything query!

That said, you *can* get the desired results in a single query. It
seems a very strange thing to want to do, but you can create a Query
based on your table; don't select any fields from the table at all.
Instead put two calculated fields in the query:

FromNY: IIF([State] = "NY", 1, 0)
NamedJoe: IIF([FirstName] = "Joe", 1, 0)

This query will show two columns, mostly zeros, with 1 in the first
column for any record from NY, 1 in the second column for each Joe.

Change the Query to a Totals query by clicking the Greek Sigma icon.
Change the default Group By field on the totals row to Sum. Run the
query; you'll see two fields in one record:

SumOfFromNY: <a count of the NY records>
SumOfNamedJoe: <a count of Joes>



.

 

[John Vinson]

Thanks. How do I create subreports? If these are just
regular reports that happen to get called into another
report I don't see where the data for a report can be
another report. ???

Open the Report in design view. Use the View menu to view the toolbar,
if it's not there already. Be sure the wizard (magic wand) icon is
selected; then select the "Subform/Subreport" tool and drag it onto
the report where you want it. Follow the wizard prompts...