Solved

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

Posted on 2004-08-12
9
208 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:pepsi_cola
Comment Utility
Could you give me an example on how that works.  (I'm new to Access and SQL...)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now