One tbl, two count qry's- can the results be displayed by a third qry?

[RobertIngles]

I have a table for which I have created two count queries based on the [EncryptionStatus] field and the [VirusProtectionStatus] field.

The first query shows the count of encryption status's in the table - counts of five possible categories.
The second query shows the count of Virus protection status's in the table - counts of three possible categories.

I would like to display the results of both queries either in a single query or in a form - ultimatley one datasheet showing the results of the two queries. A report doesn't help as the user who needs the info cuts and pastes the results from my DB into his Excel spreadsheet.

I have read up on union queries however it doen't seem to fit this scenario.

Any suggestions are appreciated.

 

[randy700]

How about showing the SQL for the 2 queries you have?


Randy

 

[RobertIngles]

I created the query in design view of MS Access but here is the resulting sql code:


SELECT TBL_AVEPP_Phase2_Devices.VirusProtectionProduct, Count(TBL_AVEPP_Phase2_Devices.VirusProtectionProduct) AS CountOfVirusProtectionProduct
FROM TBL_AVEPP_Phase2_Devices
GROUP BY TBL_AVEPP_Phase2_Devices.VirusProtectionProduct
HAVING (((TBL_AVEPP_Phase2_Devices.VirusProtectionProduct) Is Not Null))
ORDER BY TBL_AVEPP_Phase2_Devices.VirusProtectionProduct;

SELECT TBL_AVEPP_Phase2_Devices.EncryptionProduct, Count(TBL_AVEPP_Phase2_Devices.EncryptionProduct) AS CountOfEncryptionProduct
FROM TBL_AVEPP_Phase2_Devices
GROUP BY TBL_AVEPP_Phase2_Devices.EncryptionProduct
ORDER BY TBL_AVEPP_Phase2_Devices.EncryptionProduct;

 

[PHV]

What about this ?

SELECT 'Encryption' AS Type, EncryptionProduct AS Product, Count(EncryptionProduct) AS CountOfProduct
FROM TBL_AVEPP_Phase2_Devices
GROUP BY EncryptionProduct
UNION
SELECT 'VirusProtection', VirusProtectionProduct, Count(VirusProtectionProduct)
FROM TBL_AVEPP_Phase2_Devices
WHERE VirusProtectionProduct Is Not Null
GROUP BY VirusProtectionProduct

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886