Multiple COUNTs in a single Query

[kit_sune]

Hey all, I tend to explain to much so I’m going to just throw it out there.

I need to produce a Data Integrity report that lists the users of a separate database and the count of errors that they make, separated by error type.

Currently my query displays all the users who made errors, and the total errors they made. But I don’t want the total - I want to break this number down by the types of errors that are made.


I modified the SQL to make it easier to read. How can I take the below statement and make a few expressions that count up the specific values? The field I need to dissect is “Type Error” and a couple of examples of error types are “A1” and “B1”

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]SELECT DISTINCTROW [Errors Table].User, Count([Errors Table].[Type Error]) AS [Errors][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]GROUP BY [Errors Table].User[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]ORDER BY [Errors Table].User;

I’ve seen it done with multiple queries joined together, but I’d like to avoid that if possible…

Thanks for your help,

~Kit.

 

[pbaldy]

Does this work?

SELECT [Errors Table].User, [Errors Table].[Type Error], Count(*) AS Errors
FROM [Errors Table] INNER JOIN [Workcenter Profiles] ON [Errors Table].PWC = [Workcenter Profiles].PWC
WHERE ((([Errors Table].[Review Status])="Error Corrected")) OR ((([Errors Table].[Review Status])="Error Not Corrected")) OR ((([Errors Table].[Review Status])="Error Not Correctable "))
GROUP BY [Errors Table].User, [Errors Table].[Type Error]
ORDER BY [Errors Table].User, [Errors Table].[Type Error]

I assume the second table has a purpose in the real query.

 

[kit_sune]

I think that would give me a total of all errors. I want to create new fields called "A1" and "B1" (and so on for the types of errors I have) that count up all the values that are equal to that error type for each User. The Field "Type Error" gives the error type (A1 or B1 ect.)

basically, when put in a table it should look something like this:

Data:
 
User |Error Type
Joe  |A1
Joe  |B1
Joe  |B1
Joe  |B1
Jim  |B1
 
Query:
User | A1 | B1 |
Joe  |  1  |  3  |
Jim  |  0  |  1  |

 

[pbaldy]

You think, but didn't try? It should produce:

Joe A 1
Joe B 3
Jim B 1

If you want the horizontal look, try the crosstab query wizard.

 

[kit_sune]

I played with the crosstab query wizard as you suggested but I don't think that's going to work.
I'm not trying to take current fields and have them used as headers, I'm trying to create new fields that count the total based off of one field. For every instance Joe has a record that shows "A1" I want it to count and display the total for that user.
I think I almost have it below - however this doesn't count only "A1" and "B1" it displays the total errors... just two times.

[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]SELECT DISTINCTROW [Errors Table].USER, [Errors Table].[Type Error], Count([Type Error]="A1") AS [A1], Count([Type Error]="B1") AS B1[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM [Errors Table][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]GROUP BY [Errors Table].USER, [Errors Table].[Type Error][/FONT][/COLOR]
[COLOR=black][FONT=Verdana]HAVING ((([Errors Table].[Type Error]) Is Not Null));[/FONT][/COLOR]

 

[Brianwarnock]

This can be done with the Sum(IIIF. approach

Sum(Iif(errortype="A1",1,0))as counta1

But this is non dynamic and will not automatically catch new codes

Brian