0 Counts in a Make-Table Query

[MarjiS]

I am counting closed help tickets and grouping them by the assigned technician from an issue tracking table using a make-table query. If a tech has no closed tickets, the table doesn't list him and I need the table to list all techs even if the total is zero. I tried using an IIF suggestion from another thread and it didn't work; I haven't figured out how to use Nz. The query SQL code is:

SELECT dbo_Issues.AssignedTo, Count(dbo_Issues.SubmittedDate) AS CountOfSubmittedDate INTO TClosedAll
FROM dbo_Issues
WHERE (((dbo_Issues.ClosedDate) Is Not Null))
GROUP BY dbo_Issues.AssignedTo;

Thanks for your time.
MarjiS

 

[lespaul]

do you have a table that contains the names of all the techs?

SELECT dbo_Issues.AssignedTo, Count(dbo_Issues.SubmittedDate) AS CountOfSubmittedDate INTO TClosedAll
FROM dbo_Issues
RIGHT JOIN TechNameTable ON dbo_Issues.AssignedTo = TechNameTable.MatchingFieldofAssignedTo
WHERE (((dbo_Issues.ClosedDate) Is Not Null))
GROUP BY dbo_Issues.AssignedTo;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MarjiS]

Thanks for your reply, Leslie.

Unfortunately not. If that's the solution, however, another make-table query will create one.

MarjiS

 

[lespaul]

if you want to be able to report "missing" information, you need a "source" of all available information (in this case all the techs that may have been assigned) in order to determine which of them doesn't have any information.

Check out the link below on joins to understand how that all works.

Good luck!


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MarjiS]

Thanks again!
Marji

 

[PHV]

And What about this ?
SELECT AssignedTo, Sum(IIf([ClosedDate] Is Null, 0, 1)) AS CountOfSubmittedDate INTO TClosedAll
FROM dbo_Issues
GROUP BY AssignedTo;

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

 

[lespaul]

Show off!