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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|