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