Using MS Access/Crosstab Query Union?


QUESTION: Hi there,
 I have to produce a Monthly Summary report which pulls information from several different queries.  I am trying to use a Crosstab Query as the 12 months are my column headings and then I have several events as my row headings.  As an example I have to find 3 Events:  1. The Total Number of Inspections in a Month, 2. The Number of those inspections that resulted in an Out of Service Status and 3. The Rate by dividing the number of OOS Inspections by the Total number of Inspections.  Is this even possible in Access or should we just run several queries to get the numbers and then plug them into a spreadsheet to do the additional calculations?  

Thank you,

ANSWER: Hard to answer without knowing the structure of the data. If you can return all the records you need using criteria in one query, you can then use that query as the source of your crosstab.

If you need to create several queries, you may be able to do a Union as the source of your crosstab.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

 I think I solved part of my problem.  I did create separate queries to get the Total Number of Inspections, and the OOS Inspections.  Then I did a Union Query to merge the 2 together.  Next I created a Crosstab Query using the Union data but I have to switch the Months to the Row Headings and the Events to the Column Headings.  Finally I created another query using the Crosstab data where I was able to divide the OOS Inspections by the Total Inspections to get my Rate.  (So far so good)  Now the only problem is that the Months are no longer the Column Headings, and I don't think I can bring the other data into my report as some of my other sections contain Sum Values and others contain Count Values. I can only seem to do one or the other, not both.

Can you show me the SQL of the Union query. Not sure why you had to use the months as the Rows.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.