Simple Count query not working out 2

[jahlmer]

This is a aingle table, simple query problem, I think. I am prepared to feel and look stupid with your response, any response, and any good Access/VBA/Sql resources online you can recommend will be greatly appreciated!

Within one table, there are two columns I need to report on.

tblRecords

Badge , Type
0001 , Call In
0001 , Call Out
0002 , Call In
0002 , Call In
0002 , Call Out
0003 , Call Out
0003 , Call Out


What I am needing is something like this

Badge , Call In , Call Out
0001 , 1 , 1
0002 , 2 , 0
0003 , 0 , 3

I've tried the following:

SELECT Badge, count(Type = "Call In") as CallIn, count(Type = "Call Out") as CallOut
from tblRecords
Group by Badge;

and

SELECT Badge, (SELECT count(*) from tblRecords where Type = "Call In") as CallIn, (SELECT count(*) from tblRecords where Type = "Call Out")as CallOut
from tblRecords
Group by Badge;

and all I can get is something like this:

Badge , CallIn , CallOut
0001 , 2 , 2
0002 , 3 , 3
0003 , 2 , 2

To say the truth, I've tried 50 ways and dozens of Google searches and still haven't found the simple answer.

Thank you

 

[lespaul]

I think you need to correlate your subqueries:

SELECT Badge, (SELECT count(*) from tblRecords r2 where Type = "Call In" AND R1.Badge = R2.Badge) as CallIn, (SELECT count(*) from tblRecords R3 where Type = "Call Out" AND R1.Badge = R3.Badge)as CallOut
from tblRecords r1
Group by Badge;

Leslie

 

[PHV]

Have you tried this ?
SELECT Badge, Sum(Abs([Type]="Call In")) As CallIn, Sum(Abs([Type]="Call Out")) As CallOut
FROM tblRecords
GROUP BY Badge;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jahlmer]

Leslie - Your answer works (almost) perfectly, it teaches me something special about queries I had no clue about. The query response takes slightly longer than the equally functional answer provided by PHV but I plan to hold onto it for future reference.

PHV - The solution seems extremely (too?) simple and comes up 3 times faster than the alternative. It is the preferred solution.

I need to thank each of you for your functional answers. This has taught me a lot and I wish I had just posted this last month BEFORE I pulled out all my hair. It sure proves that there's more than one way to cook a goose even if it is hard to catch.

Kudos to all

 

[JonFer]

This is a small change to PHVs SQL - should run faster on large tables:

SELECT Badge, -Sum([Type]="Call In") As CallIn, -Sum([Type]="Call Out") As CallOut
...