Simple Count Query

[Listerman]

Ok so I am attempting to create a simple count query and have had some issues.

The table looks like so:

and I need the query to display the count for user: Nick, no and yes.
So the result would be:
Nick - Yes - 2
Nick - No - 5

and so forth for each user.

Is this possible in one query?

 

[boblarson]

Do it this way (instead of count):

Create a column which says:

NO:IIf([Escalated]="No",1,0)

and one for YES

YES:IIf([Escalated]="Yes",1,0)

Then group by user and select SUM in the NO and YES columns.

 

[pbaldy]

Try

SELECT User, Escalated, Count(User) As HowMany
FROM TableName
GROUP BY User, Escalated

 

[boblarson]

Either way should work for you. How it displays will be slightly different.

Paul's will display as you originally requested:
Nick - Yes - 2
Nick - No - 5


Mine would display:

User  No  Yes
Nick   5   2

 

[Listerman]

boblarson, id like to see your ouput but i keep receiving errors for some reason

Code:
SELECT Table1.User, Sum(IIF([Escalated]="No",1,0)) AS Resolved, Sum(IIF([Escalated]="Yes",1,0)) AS Escalated
FROM Table1
GROUP BY Table1.User;

 

[boblarson]

What is the error?

 

[boblarson]

If your column name is Escalated then you can't use that as the column name. This works in my sample database:

SELECT Table1.UserName, Sum(IIf([Escalated]="No",1,0)) AS Resolved, Sum(IIf([Escalated]="Yes",1,0)) AS Escalated1
FROM Table1
GROUP BY Table1.UserName;

 

[Listerman]

I actually changed it to
SELECT Table1.User, Sum(IIf([Table1]![Escalated]="No",1,0)) AS Resolved, Sum(IIf([Table1]![Escalated]="Yes",1,0)) AS Escalated, ([Resolved]/([Escalated]+[Resolved])) AS ATR
FROM Table1
GROUP BY Table1.User;

And it worked perfectly.

 

[boblarson]

Glad to hear you have it the way you want.

 

[Listerman]

Another quick question. I have ATR displaying as (Resolved / (Resolved + Esclation) * 100) for a percentage. How can I add % to the end of the results?

 

[boblarson]

([Resolved]/([Escalated]+[Resolved])) & "%" AS ATR

 

[Listerman]

oh yah, should of figured. thanks again

 

[steve.mccall1]

Hi,

I have a problem along the same lines and was wondering if you could help.

Well, it is basically the same problem but I want seperate columns to count if a date has been filled in or not and if it is in a specific range i.e.

ref user F closed date
1 JK 01/10/2001
2 JK
3 SM 07/02/2008
4 SM

Will become

User Files Open closed this month
JK 1 0
SM 1 1

I've had a play around using NOT NULL with the above examples but can't seem to get the syntax right. Any help would be greatly appreciated!

Steve

 

[boblarson]

It is just:

First Column:

Open:IIf(IsNull([ClosedDate]),1,0)

Second column:

Closed This Month:IIf(IsNull([ClosedDate]),0,1)

Turn on Grouping in the query and select SUM for the two columns and leave GROUP BY in the User field.

 

[pbaldy]

Closed This Month:IIf(IsNull([ClosedDate]),0,1)

Or maybe

ClosedThisMonth: IIf([ClosedDate] Between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0),1,0)

 

[steve.mccall1]

Thanks guys, that's a big help.