By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 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
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,186
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
  1. SELECT [ACNO], [AKDOK], Count([AKDOK]) AS [Failed]
  2. FROM TAKSIE
  3. WHERE [ADAT]<>" "
  4. GROUP BY [ACNO], [AKDOK]
Nov 23 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [TAKSIE].[ACNO],
  3. DCount("[AKDOK]","[TAKSIE]","[ACNO]=" & [ACNO]) AS [1st Demand],
  4. Sum(IIf([AKDOK]='SABC.RTF', 1, 0) AS CountSABC,
  5. Sum(IIf([AKDOK]='FPTP.RTF', 1, 0) AS CountFPTP, 
  6. Sum(IIf([AKDOK]='PTC.RTF', 1, 0) AS CountPTC
  7. FROM TAKSIE
  8. WHERE [TAKSIE].[ADAT]<>" "
  9. GROUP BY [TAKSIE].[ACNO];
  10.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.