JJ -

Because your three queries can return different rows (a manager may not
appear on all three queries if there are no projects in a particular status),
you will need to start with a list of managers, then combine that with your
three queries in outer joins to get the whole picture.

BaseQuery:
Select Distinct [SUPPORT MGR] from [Master Table];

Your final query will look something like this (a, b, c are the names of
your three queries):

SELECT BaseQuery.[Support MGR], a.[# COMPLETED], b.[# IN FOLLOW-UP], c.[#
PENDING]
FROM (((BaseQuery LEFT JOIN a ON BaseQuery.[SUPPORT MGR] = a.[SUPPORT MGR])
LEFT JOIN b ON BaseQuery.[SUPPORT MGR] = b.[SUPPORT MGR])
LEFT JOIN c ON BaseQuery.[SUPPORT MGR] = c.[SUPPORT MGR]);

--
Daryl S


"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

Can this be done? I’ve tried query joins, unions, reports with subreports,
etc. but nothing I’ve tried gives me the results I’m after.

JJ

PS – If this matters, I am using Access ’97.