Joining 3 Queries That Count
in [Queries]
|
From: Jerry Whittle on 28 May 2010 14:54 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. > > >
From: jjones on 28 May 2010 14:54 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. > >
From: John Spencer on 28 May 2010 15:26 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. >>>
From: jjones on 28 May 2010 15:35 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. > > > >
First
|
Prev
|
Pages: 1 2 Prev: Next to Last Date Next: How to find text that is surrounded by a wild card, specificly |