Colum that has more than 1 comparison (in where clause) that must be counted

[Bennie]

My problem is the following:

I need to count the number of diffrent letters that were printed for each account.
To do this for one value of a colum is easy in the where clause:
SELECT Count([TAKSIE].[AKDOK]) AS [1st Demand], [TAKSIE].[ACNO]
FROM TAKSIE
WHERE ([TAKSIE].[ADAT]<>" ") AND ([TAKSIE].[AKDOK] = "SABC.RTF")
GROUP BY [TAKSIE].[ACNO]

This query will tell me there is for acount number (ACNO) AA0000 10 SABC.RTF letters.

But now I want to know the where (ACNO) AA0000 has 10 SABC.RTF letters,
How many FPTP.RTF (failed ptp letters does he have, and PTC.RTF, and SREM.RTF ec.)

How do I write a query that will combine all this information in one query?
A UNION, only tells for each account individually how many letters he has.
I need in one row, the account, then the number of SABC.RTF (letters), FPTP.RTF (letters), PTC.RTF (Letters)


I have tried the following but it doesn't give me the correct totals:

SELECT Count([TAKSIE].[AKDOK]) AS [FailedPTP], Count([TAKSIE].[AKDOK]) AS [PTC], Count([TAKSIE].[AKDOK]) AS [1stDemand],
[TAKSIE].[ACNO]
FROM TAKSIE
WHERE [TAKSIE].[ADAT]<>" " AND ( [FailedPTP] = "FPTP.RTF") AND ( [PTC]= "PTC.RTF") AND ( [PTC]= "SABC.RTF")
GROUP BY [TAKSIE].[ACNO]

however it sees the values pf [failedPTP] as input that the user muss give..

[NeoPa]

Try this, but I suspect the WHERE clause will fail.
Let me know what type of field [ADAT] is and what you want done with it and I'll revise the SQL for you.

SELECT [ACNO], [AKDOK], Count([AKDOK]) AS [Failed]
FROM TAKSIE
WHERE [ADAT]<>" "
GROUP BY [ACNO], [AKDOK]

[MMcCarthy]

 
SELECT [TAKSIE].[ACNO],
DCount("[AKDOK]","[TAKSIE]","[ACNO]=" & [ACNO]) AS [1st Demand],
Sum(IIf([AKDOK]='SABC.RTF', 1, 0) AS CountSABC,
Sum(IIf([AKDOK]='FPTP.RTF', 1, 0) AS CountFPTP, 
Sum(IIf([AKDOK]='PTC.RTF', 1, 0) AS CountPTC
FROM TAKSIE
WHERE [TAKSIE].[ADAT]<>" "
GROUP BY [TAKSIE].[ACNO];
 

Try this ...

[Bennie]

Thx alot, that really helped alot. I always wondered if you could use a If-statement with a query. That just solved alot of my problems!