Applying multiple count functions with WHERE criteria without affecting the others

[Wesland]

Hello,

I am wondering if there is any way to use multiple count functions in a query with their own individual filters without affect the others?

For example:

SELECT [E&I Table].System, [E&I Table].DeleteRecord, Count([E&I Table].[Status#1]) AS [CountOfStatus#1], Count([E&I Table].[Status#2]) AS [CountOfStatus#2]
FROM [E&I Table]
WHERE ((([E&I Table].[Status#1]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#1]) Like "Completed" Or ([E&I Table].[Status#1]) Like "Ready for T/O" Or ([E&I Table].[Status#1]) Like "Reviewed by JVV") AND (([E&I Table].[Status#2]) Like "ITR Rcv'd by QA" Or ([E&I Table].[Status#2]) Like "Completed" Or ([E&I Table].[Status#2]) Like "Ready for T/O" Or ([E&I Table].[Status#2]) Like "Reviewed by JVV"))
GROUP BY [E&I Table].System, [E&I Table].DeleteRecord;

I am trying to count in each column of the E&I table with criteria using WHERE but the problem is when you have more than 1 WHERE it affects the other columns as well... tried a bunch of different ways and I am now having to create seperate Queries than combine them using another Query... any ideas...

 

[plog]

Yes, you move the criteria from the WHERE clause to an IIf statement inside a SUM. Your SQL is too messy for me to understand so here's an example:

StudentScores
StudentID, StudentGender, StudentAge, StudentScore
2, F, 10, 99
12, M, 9, 80
13, M, 9, 91
4, F, 9, 77

Suppose we want scores and student counts by gender:

SELECT SUM(IIF(StudentGender="F", 1,0)) AS FemaleStudentCount, SUM(IIF(StudentGender="F", [StudentScore],0)) AS FemaleStudentTotalScore
FROM SampleTable

That query would return this:

FemaleStudentCount, FemaleStudentTotalScore
2, 176

You can make the IIF more complex by adding more criteria (you could do females under 10: SUM(IIF(StudentGender="F" AND StudentAge<10, 1,0)) AS FemaleStudentCount). For counts, the key is to use 1 if the record is true and 0 if the record is false--then you sum up all those 1s and 0s and you have your total count.

 

[Galaxiom]

I woudld strongly advise you to desist from using special characters such as the # in field or table names. Also use CamelCase instead of spaces in names.

Rather than storing the Status as text you should store numbers. The text that the numbers represent belong in a lookup table. This structrue is faster to query and more compact to store.

You are using Like unnecessarily. Comparison with a string that does not contain wildcard characters should be done with equals.

Your numbered Status field names suggest a data structure normalization error. I have not looked at your query but it could well be part of the problem you have with the query.

 

[plog]

Galaxiom, to make the insightful post you did, you did technically look at the query. I didn't even do that. Had, I, I wouldn't have made the post I did.

Now that I have looked at it, I agree completely with everything Galaxiom said. My initial post is a band-aid on our problem. I would follow Galaxioms recommendations to rename and restructure your tables.

 

[Wesland]

Thanks for the reply guys, I have some work ahead of me.

To simplify the query:

Table:

ITR1 Status1 ITR 2 Status 2
EI-28-01 Received EI-28-02 Not Started
EI-15-01 Completed EI-15-03 Received
EI-13-03 Added EI-15-01 Not Started

I want to Count the values in the columns excluding say Not started. I used a Query built like this (attached). Is there an easy way in the meantime?

 

[Galaxiom]

Continuing with the current data structure will be a waste of time as you will run into more and more difficulties.

You need to bite the bullet and normalizse the structure by moving the repeating fields to a related table. Then the queries will be simple.

 

[Wesland]

ok. Thanks! I will come back if I am having issues after I fix the current problem at hand.