Solved

How to add results from multiple queries onto a single report.

Posted on 2004-08-12
9
212 Views
Last Modified: 2008-03-06
I am totally new to Microsoft Access.  What I want to know; is there a way to get multiple results from a query that filters out data to a report?  What I mean by that is that I have a single table where my data resides in.  From this table, I have generated 37 queries to filter out my data.  I want to be able to generate a report where 3 to 4 results of these queries are printed to a report.  Also to display the count of records per query at the end of that query on the report.  For example, I want 3 queries to appear in a report as follows:

LAST              FIRST             INITIAL   ADDRESS   STATE   ZIP      HOME PHONE    WORK  PHONE   REGION    DISTRICT
[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_1_Total]

[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_2_Total]

[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_3_Total]

Any help or guidance in this simple request would be greatly appreciated.
0
Comment
Question by:pepsi_cola
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:DataMaat
ID: 11783692
Hello,

When the fieldnames are the same, you can past your queries in the SQL window and between them use UNION

example:
[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_1_Total]
UNION
[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_2_Total]
UNION
[Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_3_Total]

DataMaat (Holland)
0
 

Author Comment

by:pepsi_cola
ID: 11784084
I have used the 'UNION' SQL statement and it works, but the output of the queries are all mixed up within each other.  Perhaps I need to clarify what I want a bit more.  I want to be able to keep the result, say of query1, sorted by [Last_Name] and kept within that output.  In other words, I do not want to combine the output of the 3 queries all combined.  I want them listed seperatley in alpabetical order by last name per query.  Also, how can I get the count of number of records per query?  I'm not too familiar with SQL yet (I'm currently taking a Database Course to get me spun up.)  I apologize for the confusion.  Any more insight you can give me will be greatly appreciated.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11784175
You could create a calculated field in each of your queries that gives it's name to all the record in that query, then sort by that field. for Example:

"Qry1" as Category [Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_1_Total]
UNION
"Qry2" as Category [Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_2_Total]
UNION
"Qry3" as Category [Last_Name]   [First_Name]   [Initial]   [Address]   [State]   [Zip]   [Home_Phone]   [Work_Phone]   [Region]   [District]
Total: [qry_3_Total]

Now if you sort by Category, then Last_Name you should be able to get results the way you want.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:pepsi_cola
ID: 11789434
Could you give me an example on how that works.  (I'm new to Access and SQL...)
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 500 total points
ID: 11791262
you would actually need to create 2 queries, one your Union query that contains all your fields (including a calculated field) then another to sort it. Using a sample customer table I created the following Union query:

SELECT Customers.[Company Name], Customers.[Contact Name], Customers.[Contact Title], Customers.Address, Customers.City, Customers.Region, "1" AS Category
FROM Customers
WHERE (((Customers.[Company Name]) Like "A*" Or (Customers.[Company Name]) Like "D*" Or (Customers.[Company Name]) Like "G*"));
Union
SELECT Customers.[Company Name], Customers.[Contact Name], Customers.[Contact Title], Customers.Address, Customers.City, Customers.Region, "2" AS Category
FROM Customers
WHERE (((Customers.[Company Name]) Like "B*" Or (Customers.[Company Name]) Like "E*" Or (Customers.[Company Name]) Like "H*"));
UNION SELECT Customers.[Company Name], Customers.[Contact Name], Customers.[Contact Title], Customers.Address, Customers.City, Customers.Region, "3" AS Category
FROM Customers
WHERE (((Customers.[Company Name]) Like "C*" Or (Customers.[Company Name]) Like "F*" Or (Customers.[Company Name]) Like "I*"));

I then created another query that sorted this first  by Category, then by Company name.

I posted a copy of the database I created this in here, so you can see it in action:

http://www.geocities.com/will_scarlet7/SampleMDB.html
0
 

Author Comment

by:pepsi_cola
ID: 11795266
That is almost exactly what I want.  The query that you created calles "CatUnionSort", is there a way to make a report from that query, to show the 'counts' per category?  For example:

Company Name      Contact Name      Contact Title      Address      City      Region      Category
Ana Trujillo Emparedados y helados      Ana Trujillo      Owner      Avda. de la Constitución 2222      México D.F.            1
Antonio Moreno Taquería      Antonio Moreno      Owner      Mataderos  2312      México D.F.            1
Around the Horn      Thomas Hardy      Sales Representative      120 Hanover Sq.      Londoun            1
Die Wandernde Kuh      Rita Müller      Sales Representative      Adenauerallee 900      Stuttgart            1
Drachenblut Delikatessen      Sven Ottlieb      Order Administrator      Walserweg 21      Aachen            1
Du monde entier      Janine Labrune      Owner      67, rue des Cinquante Otages      Nantes            1
Galería del gastrónomo      Eduardo Saavedra      Marketing Manager      Rambla de Cataluña, 23      Barcelona            1
Godos Cocina Típica      José Pedro Freyre      Sales Manager      C/ Romero, 33      Sevilla            1
Gourmet Lanchonetes      André Fonseca      Sales Associate      Av. Brasil, 442      Campinas      SP      1
Great Lakes Food Market      Howard Snyder      Marketing Manager      2732 Baker Blvd.      Eugene      OR      1
GROSELLA-Restaurante      Manuel Pereira      Owner      5ª Ave. Los Palos Grandes      Carakas      DF      1

0
 

Author Comment

by:pepsi_cola
ID: 11795301
The previous post was posted by accident.   It was not finished.  It continues here...

For example:

Company Name      Contact Name      Contact Title      Address      City      Region      Category
Ana Trujillo Emparedados y helados      Ana Trujillo      Owner      Avda. de la Constitución 2222      México D.F.            1
Antonio Moreno Taquería      Antonio Moreno      Owner      Mataderos  2312      México D.F.            1
Around the Horn      Thomas Hardy      Sales Representative      120 Hanover Sq.      Londoun            1
Die Wandernde Kuh      Rita Müller      Sales Representative      Adenauerallee 900      Stuttgart            1
Drachenblut Delikatessen      Sven Ottlieb      Order Administrator      Walserweg 21      Aachen            1
Du monde entier      Janine Labrune      Owner      67, rue des Cinquante Otages      Nantes            1
Galería del gastrónomo      Eduardo Saavedra      Marketing Manager      Rambla de Cataluña, 23      Barcelona            1
Godos Cocina Típica      José Pedro Freyre      Sales Manager      C/ Romero, 33      Sevilla            1
Gourmet Lanchonetes      André Fonseca      Sales Associate      Av. Brasil, 442      Campinas      SP      1
Great Lakes Food Market      Howard Snyder      Marketing Manager      2732 Baker Blvd.      Eugene      OR      1
GROSELLA-Restaurante      Manuel Pereira      Owner      5ª Ave. Los Palos Grandes      Carakas      DF      1
Count for Category 1:  11


What I want is a count of records per category and the end of each category on a report.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11795979
If in your report you have it grouped by Category, then you turn on the group footer, you can insert a count or sum function in a text box in the group footer.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11795992
I can post an example of this as well if you need it, but I have to go out for an hour or so, so it will have to wait til I get back.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question