Change the PIVOT clause to

PIVOT [Name of Statusn Field] in ("Completed","Follow-up","Pending")

Specifying the column names forces the column to appear whether or not data
exists for the column. Doing so, also causes any other additional columns to
NOT be displayed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

jjones wrote:
Also, is it possible to avoid the #Name? errors on the form based on the
crosstab query if one or more of those statuses are not found in the source
query at the moment? For example, if there currently are no records in
"Follow-Up", then it obviously does not generate any records in the crosstab
query...hence the #Name? error on my form. Can it just show "0" when this is
the case instead of the #Name? error?

"Jerry Whittle" wrote:

I'd create a union query like below. Then use it as the record source for a
crosstab query.

SELECT [Master Table].[SUPPORT MGR],
"Completed",
Count([Master Table].[MERCHANT ID]) AS TheCount
FROM [Master Table]
WHERE [Master Table].DC Between [Start Date] And [End Date]
GROUP BY [Master Table].[SUPPORT MGR]
UNION ALL
SELECT [Master Table].[SUPPORT MGR],
"Follow-UP",
Count([Master Table].[MERCHANT ID])
FROM [Master Table]
WHERE [Master Table].[Follow-up Date] Is Not Null
AND [Master Table].DC Is Null
GROUP BY [Master Table].[SUPPORT MGR]
UNION ALL
SELECT [Master Table].[SUPPORT MGR],
"Pending",
Count([Master Table].[MERCHANT ID])
FROM [Master Table]
WHERE [Master Table].[Follow-up Date] Is Null
AND [Master Table].DC Is Null
GROUP BY [Master Table].[SUPPORT MGR];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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.