COUNT Complex SELECT

[gregz0012]

Hello,

I am trying to get the results similar to the below with this...

UserID | Exp1 | Exp2 | Exp3 | Exp4
-----------------------------------
123 | 1 | 9 | 7 | 3
321 | 6 | 2 | 0 | 2

With the following:

SELECT
(SELECT COUNT(StatusID) FROM Callbacks WHERE StatusID = 6),
(SELECT COUNT(StatusID) FROM Callbacks WHERE StatusID = 5),
(SELECT COUNT(StatusID) FROM Callbacks WHERE StatusID = 4),
(SELECT COUNT(StatusID) FROM Callbacks WHERE StatusID = 3),
Callbacks.UserID
FROM
Callbacks

In English I want each user to be listed and each entitiy with that user and the Count for specific values for StatusID to be shown also. But how? All I can get is a total for all.

 

[PHV]

Perhaps this ?
SELECT UserID
,Sum(IIf(StatusID = 6),1,0) AS S6
,Sum(IIf(StatusID = 5),1,0) AS S5
,Sum(IIf(StatusID = 4),1,0) AS S4
,Sum(IIf(StatusID = 3),1,0) AS S3
FROM Callbacks
GROUP BY UserID

You may also try to follow the CrossTab query wizard.


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

 

[gregz0012]

That query works to some degree but only displays 1 or 0 not the total sum of (StatusID = 6) etc.

I don't think I can use the crosstab query wizard, I need this query to go into a program using ADO components.

 

[dhookom]

You typed the current values but not the desired display.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gregz0012]

dhookom:I'm not sure what you mean by that but any help on this would be appreciated.

 

[dhookom]

I transposed and should have stated: You showed us what you want the display to be but we don't know your current structure.

I would expect a crosstab to work but can't be sure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]