Solved

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

Posted on 2004-08-12
9
209 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 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 …

895 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

16 Experts available now in Live!

Get 1:1 Help Now