Joining 3 Queries That Count

J

jjones

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

Wolfgang Kais

Hello JJ.

jjones said:
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
Click to expand...

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];
 
D

Daryl S

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]);
 
J

Jerry Whittle

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];
 
J

jjones

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

Jerry Whittle

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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

jjones

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 said:
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 said:
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.
Click to expand...
Click to expand...
 
J

John Spencer

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
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
J

jjones

Yep, that worked--now if I can just get rid of those #Name? errors (see other
post) I'll be all set. I tried to invent my own workaround by just entering
3 dummy records, 1 for each possible status, and then just putting something
in the name field like "placeholder". Then I was going to apply a filter on
the actual form to not show the name "placeholder". I couldn't get the
filter work...and I don't know if this is really a viable workaround anyway.
Got a better idea? I prefer the empty fields to either show zero or just be
blank instead of seeing that #Name? all the way down the page.

Jerry Whittle said:
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 said:
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 said:
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.


:

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query by Week with Dates as Headers 5
How to populate a column in a row based on a value in another colu 3
Count Null Values Per Field 2
Add a field in a query. Easy. . .so I though. 5
INCREMENTAL NBR VS. AUTONUMBER WHEN APPENDING 2
Three reports based on query with dates in 4 fields 2
Generate OrderNo by comparing the previous orders 4
Year query based on text 3

Top