Hello JJ.

"jjones" wrote:
I am trying to decipher the "status" of records in my table based
on two date fields and the name of the agent assigned to each record.
The fields are as follows:

SUPPORT MGR (Name)
DC (Date Completed)
Follow-up Date

I have 3 separate queries based on these fields that work as follows:

a) Completed
SELECT [Master Table].[SUPPORT MGR],
Count([Master Table].[MERCHANT ID]) AS [# COMPLETED]
FROM [Master Table]
WHERE ((([Master Table].DC) Between [Start Date] And [End Date]))
GROUP BY [Master Table].[SUPPORT MGR];

b) In Follow-Up
SELECT [Master Table].[SUPPORT MGR],
Count([Master Table].[MERCHANT ID]) AS [# IN FOLLOW-UP]
FROM [Master Table]
WHERE ((([Master Table].[Follow-up Date]) Is Not Null) AND
(([Master Table].DC) Is Null))
GROUP BY [Master Table].[SUPPORT MGR];

c) Pending
SELECT [Master Table].[SUPPORT MGR],
Count([Master Table].[MERCHANT ID]) AS [# PENDING]
FROM [Master Table]
WHERE ((([Master Table].[Follow-up Date]) Is Null) AND
(([Master Table].DC) Is Null))
GROUP BY [Master Table].[SUPPORT MGR];


This works okay for seeing the statuses one at a time, but what
I really want is a table something like this:

NAME # COMPLETED # IN FOLLOW-UP # PENDING
John Doe 8 2 4
Sally Sue 5 2 7
Jane Smith 14 0
0

Not tested, hope this helps:
SELECT [Master Table].[SUPPORT MGR],
Sum(Iif([Master Table].DC Between [Start Date] And [End Date],1,0))
AS [# COMPLETED],
Sum(Iif(([Master Table].[Follow-up Date] Is Not Null) AND
([Master Table].DC Is Null),1,0)) AS [# IN FOLLOW-UP],
Sum(Iif(([Master Table].[Follow-up Date] Is Null) AND
([Master Table].DC Is Null),1,0)) AS [# PENDING]
FROM [Master Table]
GROUP BY [Master Table].[SUPPORT MGR];

--
Regards,
Wolfgang