Combining a two union queries and creating a report on those results

[m0aje]

I am using the following UNION QUERY to total up equipment tested for a report.




SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Printers Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [PRINTER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));
UNION
SELECT "Servers Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [SERVER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]));


UNION QUERY RESULTS:

Start Date 6/1/14 End Date 6/18/14


Laptops Tested 27
Workstations Tested 18
Printers Tested 22
Servers Tested 19

Works beautifully!


I created this query to actually tally up the equipment in the above query.


SELECT SUM(Records)
FROM
(
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [LAPTOPS]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]))
UNION
SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [WORKSTATION]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]))
UNION
SELECT "Printers Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [PRINTER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]))
UNION
SELECT "Servers Tested" AS PCEQUIP, Count(*) AS RECORDS
FROM [SERVER]
WHERE (((Date)Between [Enter Start Date] AND [Enter End Date]))
)

Again it works great.


I have been trying for a days to combine the results of these two queries into one report with the end result of having the PC equipment tested in a report form and the results tallied up as a total pieces of equipment tested. Is this possible? I have looked at the SQL functions and thought that OUTERJOIN may be the closest function to use, but I cannot figure out the syntax and I am not sure if that is the function to use. Does anyone know this could be achieved? Any help would be most appreciated.


Thank you.


//m0aje//

[June7]

Why don't you use the first query as report RecordSource and do the summary calc in report header/footer section with expression in textbox: =Sum([Records])?

[m0aje]

Why don't you use the first query as report RecordSource and do the summary calc in report header/footer section with expression in textbox: =Sum([Records])?

Thanks. I hadn't thought of that. I will give that a try.