Solved

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

Posted on 2004-08-12
9
221 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

632 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