Prev: Next to Last Date
Next: How to find text that is surrounded by a wild card, specificly
From: jjones on 28 May 2010 10:20
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.

From: Wolfgang Kais on 28 May 2010 11:23
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




From: Daryl S on 28 May 2010 11:27
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.
>
From: Jerry Whittle on 28 May 2010 11:44
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.
>
From: jjones on 28 May 2010 13:55
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.
> >
 |  Next  |  Last
Pages: 1 2
Prev: Next to Last Date
Next: How to find text that is surrounded by a wild card, specificly