A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Joining 3 Queries That Count



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 03:20 PM posted to microsoft.public.access.queries
jjones
external usenet poster
 
Posts: 15
Default Joining 3 Queries That Count

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.

  #2  
Old May 28th, 2010, 04:23 PM posted to microsoft.public.access.queries
Wolfgang Kais[_4_]
external usenet poster
 
Posts: 18
Default Joining 3 Queries That Count

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




  #3  
Old May 28th, 2010, 04:27 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Joining 3 Queries That Count

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.

  #4  
Old May 28th, 2010, 04:44 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Joining 3 Queries That Count

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.

  #5  
Old May 28th, 2010, 06:55 PM posted to microsoft.public.access.queries
jjones
external usenet poster
 
Posts: 15
Default Joining 3 Queries That Count

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.

  #6  
Old May 28th, 2010, 07:54 PM posted to microsoft.public.access.queries
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default Joining 3 Queries That Count

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.

  #7  
Old May 28th, 2010, 07:54 PM posted to microsoft.public.access.queries
jjones
external usenet poster
 
Posts: 15
Default Joining 3 Queries That Count

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.

  #8  
Old May 28th, 2010, 08:26 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default Joining 3 Queries That Count

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.

  #9  
Old May 28th, 2010, 08:35 PM posted to microsoft.public.access.queries
jjones
external usenet poster
 
Posts: 15
Default Joining 3 Queries That Count

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" wrote:

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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.