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
Solved

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

Posted on 2004-08-12
9
216 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

790 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