My bad! I forgot that you must declare the data type for a parameter query
if it's going to be used in a crosstab. This is something that started in
Access 2003 if I remember correctly. The very first line of the SQL statement
needs to look like this (including the semicolon).

PARAMETERS [Start Date] DateTime, [End Date] DateTime;

However I've never tried something like this in the beginning of a union
query. If it doesn't work, you may need to create query with the parameters
defined and then Union the queries. Hopefully that will work.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"jjones" wrote:

Jerry,

I decided to try your approach first because I initially wanted to set this
up as a crosstab query anyway (and then build a "pretty" form based on this
query). The union query works beautifully as written, but then the crosstab
query based on this union query loses the ability to prompt for user-defined
dates (where I have my "Between [Start Date] And [End Date]"). It gives an
error message unless I take that criteria out completely. Is it not possible
to prompt for a date range with this type of query?

JJ

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