434,871 Members | 2,575 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

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

 P: 2 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.. Nov 23 '06 #1
3 Replies

 Expert Mod 15k+ P: 31,489 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. Expand|Select|Wrap|Line Numbers SELECT [ACNO], [AKDOK], Count([AKDOK]) AS [Failed] FROM TAKSIE WHERE [ADAT]<>" " GROUP BY [ACNO], [AKDOK] Nov 23 '06 #2

 Expert Mod 10K+ P: 14,534 Expand|Select|Wrap|Line Numbers   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 ... Nov 23 '06 #3

 P: 2 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! Nov 28 '06 #4 