Is this even possible (Union query question)

[Takashi]

I want to generate monthly reports containing a count of the number of different types of invoices processed during the month.

I want the count of invoices of type "A" to end up in a field called "A", the count of type "B" in a field called "B", etc.

I tried using a union query, but the counts ended up as several different records instead of one record with several fields.

Here's what I tried (that didn't work):

SELECT Count(*) AS 48
FROM Invoice
WHERE ((type_id = "48 Agreement") and (Auditor = "Cheryl"))
UNION ALL SELECT Count(*) AS ES
FROM Invoice
WHERE ((type_id = "Travel") and (Auditor = "Cheryl"));

 

[araskas]

Union qry is not needed here.
Qry :
SELECT COUNT (
IIf(invoiceType = "A' , 1, 0 ) ) AS [Count Of Invoice Type A],
COUNT ( IIf(invoiceType = "B' , 1, 0 ) ) AS [Count Of Invoice Type B]
FROM MYTABLE
[WHERE 'any Where condition here'] ;

Let me know if it does not work.

Alternatively you can use 2 queries

Qry1 :
SELECT
IIf(invoiceType = "A' , 1, 0 ) AS [Count Of Invoice Type A],
IIf(invoiceType = "B' , 1, 0 ) AS [Count Of Invoice Type B]
FROM MYTABLE
[WHERE 'any Where condition here'] ;


Qry2 :
SELECT count ( [CountOfInvoiceTypeA]) ,

count ( [CountOfInvoiceTypeA]) FROM Qry1;

 

[Takashi]

Sorry for taking so long in replying.

As for your method, #1 works perfectly is you replace the COUNT with SUM in the SQL statement.

Thanks