counting blank recordsets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 23 Nov 2005 21:02 GMT

I have an A2003 query that uses count(*) to return the number of reviews for
an employee. Works great if you have at least 1 review; chokes when you
don't have any reviews.
How can I return the empty recordset?

copy of sql included.

SELECT [tbl-Agents].AgentID, [HISTORY-Q].Department, [HISTORY-Q].[Emp Num],
[HISTORY-Q].ReviewMonth, Count(*) AS [#AR]
FROM [tbl-Agents] LEFT JOIN [HISTORY-Q] ON [tbl-Agents].AgentID =
[HISTORY-Q].[Emp Num]
GROUP BY [tbl-Agents].AgentID, [HISTORY-Q].Department, [HISTORY-Q].[Emp
Num], [HISTORY-Q].ReviewMonth
HAVING ((([HISTORY-Q].ReviewMonth)='October') AND ((Count(*))<10));

Signature

Thanks in advance,
Michael

Reply to this Message

Michael - 23 Nov 2005 21:17 GMT

same results if I remove the tbl-Agents.
What I am looking for is a rs like
12345 Customer Service John Doe October 9
67892 Accounting Janice Smith October 0
23478 Quality Charles Doe October 7
...
...

Thanks in advance,
Michael

> I have an A2003 query that uses count(*) to return the number of reviews for
> an employee. Works great if you have at least 1 review; chokes when you
[quoted text clipped - 10 lines]
> Num], [HISTORY-Q].ReviewMonth
> HAVING ((([HISTORY-Q].ReviewMonth)='October') AND ((Count(*))<10));

Reply to this Message