Counter Queries 2

[ecannizzo]

I want to be able to make a query that does a count of something from one table and then does another count of something from the same table. So I need two counts done in the same query. Any ideas?

Ex. Select count(*) as counter from table where wo = 'Erica'

Select count(*) as counter2 from table where co = 'Erica'

(I need these to be part of the same query so when I see the results there right next to each other).

Thanks!
Erica Thanks!
Erica

 

[THoey]

This will do it...

SELECT Sum(IIf(wo='erica',1,0)) AS Expr1, 
       Sum(IIf(co='erica',1,0)) AS Expr2
FROM tblCountNames;

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ecannizzo]

Can you explain to me what that does? I am trying to use this:

SELECT Sum(IIf((WODATA.OPENDATE) Between [date] And [date2],1,0)) AS Open,
Sum(IIf((WODATA.clsdby) Between [date] And [date2],1,0)) AS Close
FROM wodata;

But its not working correctly. What is the 1,0?

Thanks!
Erica

 

[MichaelRed]

the 1,0 are the values fed to the SUM function. Basically, each statement is sayint to add a value to the field. If the "IIF" is true, the 1 is added otherwisem the 0 is added, so Terry is giving you the way to count the times in the table where the condition(s) are met. In this specific instance the two fields are each being checked for the condition and 'counted' when "True".


The QUESTION is WHAT is not working? HOW do you know "IT" is not working?

Are you getting an error? If so, WHAT error? Are you getting incorrect results? If so, HOW do you know the results are wrong?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[THoey]

You wanted a count of items matching some constraint. What that query does is look at your constraint, if it matches the constraint, it adds 1 to the sum. If not, it adds zero to the sum.

I am thinking that it is failing on the dates or the between. Try running it with literal dates instead of the parameters and see what you get. I created a test here using the "erica's" from the earlier post and it worked fine. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ecannizzo]

The problem is that when it does the first Sum() I get the correct answer, but when it does the second one, I just get 0. When I run the second query by itself, Select count..., I get 34.

I don't know what can be wrong...

Thanks!
Erica

 

[ecannizzo]

I think I figured it out. I'll let y'all know if I need more help. Thanks so much!!! Thanks!
Erica

 

[ecannizzo]

Okay, one more question. I got that part working! This is what I have:

SELECT
Sum(IIf((WODATA.openby)=[Name],1,0)) AS [Open],
Sum(IIf((WODATA.clsdby)=[Name],1,0)) AS [Close],
Sum(IIf((WODATA.respons)=[Name],1,0)) AS Responsible,
[Name] AS Empl
FROM wodata
WHERE (((wodata.OPENDATE) Between [date] And [date2]));

Now, lets say I wanted it to run for every name in the database, so instead of them having to enter a name, it just does all the names and the results would look like this:
10 10 5 Erica
20 10 2 Jim
15 8 4 Michelle

And so on...

Is this possible?

I greatly appreciate all your help! Thanks!
Erica

 

[THoey]

Try this:

SELECT 
[Name] AS Empl,
Sum(IIf((WODATA.openby)=[Name],1,0)) AS [Open], 
Sum(IIf((WODATA.clsdby)=[Name],1,0)) AS [Close], 
Sum(IIf((WODATA.respons)=[Name],1,0)) AS Responsible
FROM wodata
WHERE (((wodata.OPENDATE) Between [date] And [date2]));
GROUP BY [Name]

I didn't test it... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ecannizzo]

In this case it still asks me which [Name] to use. I don't want it to ask me for a name, I just want it to run for everyone...

Any ideas? Thanks!
Erica

 

[THoey]

What is the field in the table that the name is in? Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ecannizzo]

The field name is called: openby or clsdby (they both have the name of the person in it.) I also have another table called Employee, that has a field called Employee which I can use. Its all the same. Maybe using the other table is better (more like a lookup table or somthing)?

What I'm trying to do is find out how many tickets a person has opened, closed and were responsible for...

Thanks!
Erica

 

[ecannizzo]

I got it!!! Thanks!
Erica

 

[THoey]

Kind of rushing this so I can go to lunch, but try this:

SELECT DISTINCT EMPLOYEE.employee_name,
Sum(IIf((WODATA.openby)=EMPLOYEE.employee_name,1,0)) AS Open, 
Sum(IIf((WODATA.clsdby)=EMPLOYEE.employee_name,1,0)) AS Close, 
Sum(IIf((WODATA.respons)=EMPLOYEE.employee_name,1,0)) AS Responsible
FROM EMPLOYEE, WODATA
WHERE (((wodata.OPENDATE) Between [date] And [date2]))
GROUP BY EMPLOYEE.employee_name;

Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[THoey]

Congrats!!! ;-) But, don't be greedy... Please share what finally worked for you. I'll learn something, and someone else may come along with a similar problem. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[ecannizzo]

I'm did exactly what you told me to do (in your last post), but I figured it just seconds before you told me to do it. You were a great help. Thanks you so much! Thanks!
Erica