Query to count on multiple fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a query that is based on a table called TARGETS which returns the
number of targets in the table that are marked as "bad", and then grouped by
user. The code for this query is:

SELECT TARGETS.[User Name], Count(TARGETS.Bad) AS CountOfBad
FROM TARGETS
GROUP BY TARGETS.[User Name]
HAVING (((Count(TARGETS.Bad))=True))
ORDER BY Count(TARGETS.Bad) DESC;

What I want to do in addition to the count of bad target records created by
user name, is to show the total number of records created by each user, so
that the result is something like:

Created Bad
Mary Jones 17 4
Bob Smith 12 3
etc

What is the best way to do this?


Many thanks

Martyn
Access 2000, Windows 2003 server over Citrix MF
 
SELECT TARGETS.[User Name],
Count(TARGETS.Bad) AS CreatedTargets
Sum(Abs(TARGETS.Bad)) AS BadTargets
FROM TARGETS
GROUP BY TARGETS.[User Name]
ORDER BY Sum(Abs(TARGETS.Bad)) DESC;

I'm assuming a Yes/No field and no null values.
 
Thanks very much Jerry

That worked a treat!


Martyn

Jerry Whittle said:
SELECT TARGETS.[User Name],
Count(TARGETS.Bad) AS CreatedTargets
Sum(Abs(TARGETS.Bad)) AS BadTargets
FROM TARGETS
GROUP BY TARGETS.[User Name]
ORDER BY Sum(Abs(TARGETS.Bad)) DESC;

I'm assuming a Yes/No field and no null values.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

WembleyBear said:
Hi

I have a query that is based on a table called TARGETS which returns the
number of targets in the table that are marked as "bad", and then grouped by
user. The code for this query is:

SELECT TARGETS.[User Name], Count(TARGETS.Bad) AS CountOfBad
FROM TARGETS
GROUP BY TARGETS.[User Name]
HAVING (((Count(TARGETS.Bad))=True))
ORDER BY Count(TARGETS.Bad) DESC;

What I want to do in addition to the count of bad target records created by
user name, is to show the total number of records created by each user, so
that the result is something like:

Created Bad
Mary Jones 17 4
Bob Smith 12 3
etc

What is the best way to do this?

Many thanks

Martyn
Access 2000, Windows 2003 server over Citrix MF
Click to expand...
Click to expand...
 
Back
Top