Calculating total number of records from union query

[m0aje]

Hello,

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:

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

I have created a report using ACCESS 2000 for this union query and it satisfies the requirement.
I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?

Any help would be most appreciated on how to resolve this.

Thanks,

m0aje

 

[KenHigg]

Looks like there is not an overwhelming number of records so you could use dcount(). Do a site or google search (or Access Help), there are a number of posts on the topic -

 

[m0aje]

Hello Ken,

Thanks for your reply. Not sure if you were referring that I should use DCOUNT vice COUNT(*).

Anyway, I tired the following:
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]))
)
This gave me the results I was looking for, but I cannot how to figure out how to combine the two into the UNION QUERY in order to get the report where it counts the individual records and then total the equipment count at the bottom of the report.
Any syggestions?

Thanks,

m0aje