John,
I realized there was a related table after I created the table and added the data. As you proved the concept remains viable. As usual, there are many solutions available to reach the same or a similar result.
Duane Hookom, MVP
MS Access
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sun, 29 Mar 2015 10:55:58 +0200
> Subject: Re: [MS_AccessPros] Showing all names in crosstab query
>
> Duane-
>
> I like the creative use of NZ on the date field. But yours won't work because Agent Name is in lkupCCAIAgents. This should do it:
>
> TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([All Enrollments].HICN) AS [Total Of HICN]
> FROM lkupCCAIAgents LEFT JOIN [All Enrollments]
> ON [All Enrollments].Rep_ID = lkupCCAIAgents.ID
> WHERE (((Nz([Application_Date],#3/5/2015#)) Between #3/5/2015# And #3/7/2015#))
> GROUP BY [All Enrollments].Rep_ID, [All Enrollments].[Agent Name]
> PIVOT Format(Nz([Application_Date],#3/5/2015#),"Short Date");
>
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> On Mar 29, 2015, at 12:07 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
>
> I was actually thinking of something like the following which might require and out join.
>
>
> TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> SELECT [All Enrollments].Rep_ID, [All Enrollments].[Agent Name], Count([All Enrollments].HICN) AS [Total Of HICN]
> FROM [All Enrollments]
> WHERE (((Nz([Application_Date],#3/5/2015#)) Between #3/5/2015# And #3/7/2015#))
> GROUP BY [All Enrollments].Rep_ID, [All Enrollments].[Agent Name]
> PIVOT Format(Nz([Application_Date],#3/5/2015#),"Short Date");
>
>
> It includes Glen with 0s.
>
>
> Duane Hookom, MVP
>
> MS Access
>
> ________________________________
> > To: MS_Access_Professionals@yahoogroups.com
> > Date: Sat, 28 Mar 2015 16:22:05 +0100
> >
> > Karen-
> >
> > Duane was close in his earlier replies, but he's correct that applying
> > a filter to the left side of a RIGHT JOIN will negate the outer join.
> > Try this:
> >
> > TRANSFORM NZ(Count([AE].HICN), 0) AS CountOfHICN
> > SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([AE].HICN)
> > AS [Total Of HICN]
> > FROM lkupCCAIAgents LEFT JOIN (SELECT Rep_ID, HICN, Application_Date
> > FROM [All Enrollments]
> > WHERE [All Enrollments].Application_Date Between #2/1/2015# And
> > #2/28/2015#) AS AE
> > LEFT JOIN lkupCCAIAgents ON [AE].Rep_ID = lkupCCAIAgents.ID
> > GROUP BY lkupCCAIAgentsID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > John Viescas, Author
> >
> > On Mar 28, 2015, at 3:59 PM,
> > k2j1203@yahoo.com<mailto:k2j1203@yahoo.com> [MS_Access_Professionals]
> >
> > Hello. This is an old post that I never got to reply to because I got
> > swamped with work and never got a chance to try your suggestions. We
> > lost two people recently and I've been doing the work of three people.
> > Anyway, I need to revisit the topic because I'm not sure that I
> > understand what you all were talking about. I included the history of
> > emails, but I'll give you my question here again.
> >
> > I created a crosstab query to show how many sales a sales agent has
> > during the course of a month. Right now, when I run the query, if an
> > agent doesn't have any sales, the name doesn't show up in the row
> > heading. I need for that agent to be there showing zero sales. Here's
> > my sql and some sample data. Hopefully the sample data will display
> > correctly as a table here.
> >
> > Rep_ID Agent Name HICN Application_Date
> > 122063 Michelle A3394 3/5/15
> > 122063 Michelle B2784 3/7/15
> > 122063 Michelle G8893 3/7/15
> > 122123 Glen
> > 12252 Julie G8892 3/7/15
> >
> > SQL
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name],
> > Count([All Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] INNER JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE [All Enrollments].Application_Date Between #2/1/2015# And #2/28/2015#
> > GROUP BY [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Let me know if you need more information.
> >
> > Thanks
> > Karen
>
>
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <wrmosca@...> wrote :
> >
> > Duane
> > Well, it certainly is worth trying.
> >
> > -Bill
> >
> >
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > Hi Bill,
> >
> > I added this to the WHERE clause:
> > " OR [All Enrollments].Rep_ID Is Null "
> > in an attempt to get around the issue.
> >
> > Some type of NZ() in the PIVOT statement might be needed in order to
> > create a decent column heading.
> >
> > Duane Hookom MVP
> > MS Access
> >
> > ________________________________
> > From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > Date: Tue, 10 Mar 2015 10:57:31 -0700
> >
> > Duane
> >
> > I don't think that will work. If I remember right putting the fields
> > from the left side of a right join in the WHERE clause negates the
> > right join and forces an INNER join. What might work is to create a
> > query on Enrollments that has the WHERE clause and then use that query
> > outer joined to the agent table.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> >
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > This SQL is an educated guess:
> >
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([All
> > Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] RIGHT JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE ([All Enrollments].Application_Date Between #2/1/2015# And #2/28/2015#
> > AND [All Enrollments].Election_Type Not Like "A"
> > AND [All Enrollments].Rep_ID Not Like "*CCAI*")
> > OR [All Enrollments].Rep_ID Is Null
> > GROUP BY lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Duane Hookom MVP
> > MS Access
> >
> > ________________________________
> > To:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> >
> > Sorry. Here's the sql query.
> >
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name],
> > Count([All Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] INNER JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE ((([All Enrollments].Application_Date) Between #2/1/2015# And
> > #2/28/2015#) AND (([All Enrollments].Election_Type) Not Like "A") AND
> > (([All Enrollments].Rep_ID) Not Like "*CCAI*"))
> > GROUP BY [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Let me know if you need anymore information.
> >
> > Karen
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > Karen,
> >
> > You should set the query join to the agent names table to include all
> > of the agent names records. If you don't understand, you should provide
> > the current crosstab SQL view as well as some table/field information.
> >
> > Duane Hookom, MVP
> > MS Access
> >
> > ________________________________
> > To:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > From:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > Date: Tue, 10 Mar 2015 08:53:45 -0700
> > Subject: [MS_AccessPros] Showing all names in crosstab query
> >
> > Hello everyone. I hope everyone is doing well.
> >
> > I need some help showing data in a crosstab query. The crosstab shows
> > Agent Name as the row heading and Months as the column heading and it
> > counts applications. When I run the query only the agents with
> > applications. It doesn't show those with a zero value. How do I get
> > all of the agent names to show even if the sales equals zero?
> >
> >
> > Thanks.
> > Karen
>
> ------------------------------------
> Posted by: Duane Hookom <duanehookom@hotmail.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
>
>
>
> ------------------------------------
> Posted by: John Viescas <johnv@msn.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/
>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/join
> (Yahoo! ID required)
>
> <*> To change settings via email:
> MS_Access_Professionals-digest@yahoogroups.com
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
> <*> To unsubscribe from this group, send an email to:
> MS_Access_Professionals-unsubscribe@yahoogroups.com
>
> <*> Your use of Yahoo Groups is subject to:
> https://info.yahoo.com/legal/us/yahoo/utos/terms/
>
I realized there was a related table after I created the table and added the data. As you proved the concept remains viable. As usual, there are many solutions available to reach the same or a similar result.
Duane Hookom, MVP
MS Access
> To: MS_Access_Professionals@yahoogroups.com
> From: MS_Access_Professionals@yahoogroups.com
> Date: Sun, 29 Mar 2015 10:55:58 +0200
> Subject: Re: [MS_AccessPros] Showing all names in crosstab query
>
> Duane-
>
> I like the creative use of NZ on the date field. But yours won't work because Agent Name is in lkupCCAIAgents. This should do it:
>
> TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([All Enrollments].HICN) AS [Total Of HICN]
> FROM lkupCCAIAgents LEFT JOIN [All Enrollments]
> ON [All Enrollments].Rep_ID = lkupCCAIAgents.ID
> WHERE (((Nz([Application_Date],#3/5/2015#)) Between #3/5/2015# And #3/7/2015#))
> GROUP BY [All Enrollments].Rep_ID, [All Enrollments].[Agent Name]
> PIVOT Format(Nz([Application_Date],#3/5/2015#),"Short Date");
>
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
>
> On Mar 29, 2015, at 12:07 AM, Duane Hookom duanehookom@hotmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
>
> I was actually thinking of something like the following which might require and out join.
>
>
> TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> SELECT [All Enrollments].Rep_ID, [All Enrollments].[Agent Name], Count([All Enrollments].HICN) AS [Total Of HICN]
> FROM [All Enrollments]
> WHERE (((Nz([Application_Date],#3/5/2015#)) Between #3/5/2015# And #3/7/2015#))
> GROUP BY [All Enrollments].Rep_ID, [All Enrollments].[Agent Name]
> PIVOT Format(Nz([Application_Date],#3/5/2015#),"Short Date");
>
>
> It includes Glen with 0s.
>
>
> Duane Hookom, MVP
>
> MS Access
>
> ________________________________
> > To: MS_Access_Professionals@yahoogroups.com
> > Date: Sat, 28 Mar 2015 16:22:05 +0100
> >
> > Karen-
> >
> > Duane was close in his earlier replies, but he's correct that applying
> > a filter to the left side of a RIGHT JOIN will negate the outer join.
> > Try this:
> >
> > TRANSFORM NZ(Count([AE].HICN), 0) AS CountOfHICN
> > SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([AE].HICN)
> > AS [Total Of HICN]
> > FROM lkupCCAIAgents LEFT JOIN (SELECT Rep_ID, HICN, Application_Date
> > FROM [All Enrollments]
> > WHERE [All Enrollments].Application_Date Between #2/1/2015# And
> > #2/28/2015#) AS AE
> > LEFT JOIN lkupCCAIAgents ON [AE].Rep_ID = lkupCCAIAgents.ID
> > GROUP BY lkupCCAIAgentsID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > John Viescas, Author
> >
> > On Mar 28, 2015, at 3:59 PM,
> > k2j1203@yahoo.com<mailto:k2j1203@yahoo.com> [MS_Access_Professionals]
> >
> > Hello. This is an old post that I never got to reply to because I got
> > swamped with work and never got a chance to try your suggestions. We
> > lost two people recently and I've been doing the work of three people.
> > Anyway, I need to revisit the topic because I'm not sure that I
> > understand what you all were talking about. I included the history of
> > emails, but I'll give you my question here again.
> >
> > I created a crosstab query to show how many sales a sales agent has
> > during the course of a month. Right now, when I run the query, if an
> > agent doesn't have any sales, the name doesn't show up in the row
> > heading. I need for that agent to be there showing zero sales. Here's
> > my sql and some sample data. Hopefully the sample data will display
> > correctly as a table here.
> >
> > Rep_ID Agent Name HICN Application_Date
> > 122063 Michelle A3394 3/5/15
> > 122063 Michelle B2784 3/7/15
> > 122063 Michelle G8893 3/7/15
> > 122123 Glen
> > 12252 Julie G8892 3/7/15
> >
> > SQL
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name],
> > Count([All Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] INNER JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE [All Enrollments].Application_Date Between #2/1/2015# And #2/28/2015#
> > GROUP BY [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Let me know if you need more information.
> >
> > Thanks
> > Karen
>
>
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <wrmosca@...> wrote :
> >
> > Duane
> > Well, it certainly is worth trying.
> >
> > -Bill
> >
> >
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > Hi Bill,
> >
> > I added this to the WHERE clause:
> > " OR [All Enrollments].Rep_ID Is Null "
> > in an attempt to get around the issue.
> >
> > Some type of NZ() in the PIVOT statement might be needed in order to
> > create a decent column heading.
> >
> > Duane Hookom MVP
> > MS Access
> >
> > ________________________________
> > From: MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > Date: Tue, 10 Mar 2015 10:57:31 -0700
> >
> > Duane
> >
> > I don't think that will work. If I remember right putting the fields
> > from the left side of a right join in the WHERE clause negates the
> > right join and forces an INNER join. What might work is to create a
> > query on Enrollments that has the WHERE clause and then use that query
> > outer joined to the agent table.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> >
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > This SQL is an educated guess:
> >
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name], Count([All
> > Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] RIGHT JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE ([All Enrollments].Application_Date Between #2/1/2015# And #2/28/2015#
> > AND [All Enrollments].Election_Type Not Like "A"
> > AND [All Enrollments].Rep_ID Not Like "*CCAI*")
> > OR [All Enrollments].Rep_ID Is Null
> > GROUP BY lkupCCAIAgents.ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Duane Hookom MVP
> > MS Access
> >
> > ________________________________
> > To:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> >
> > Sorry. Here's the sql query.
> >
> > TRANSFORM Count([All Enrollments].HICN) AS CountOfHICN
> > SELECT [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name],
> > Count([All Enrollments].HICN) AS [Total Of HICN]
> > FROM [All Enrollments] INNER JOIN lkupCCAIAgents ON [All
> > Enrollments].Rep_ID = lkupCCAIAgents.ID
> > WHERE ((([All Enrollments].Application_Date) Between #2/1/2015# And
> > #2/28/2015#) AND (([All Enrollments].Election_Type) Not Like "A") AND
> > (([All Enrollments].Rep_ID) Not Like "*CCAI*"))
> > GROUP BY [All Enrollments].Rep_ID, lkupCCAIAgents.[Agent Name]
> > PIVOT Format([Application_Date],"Short Date");
> >
> > Let me know if you need anymore information.
> >
> > Karen
> > ---In
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>,
> > <duanehookom@...> wrote :
> >
> > Karen,
> >
> > You should set the query join to the agent names table to include all
> > of the agent names records. If you don't understand, you should provide
> > the current crosstab SQL view as well as some table/field information.
> >
> > Duane Hookom, MVP
> > MS Access
> >
> > ________________________________
> > To:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > From:
> > MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
> > Date: Tue, 10 Mar 2015 08:53:45 -0700
> > Subject: [MS_AccessPros] Showing all names in crosstab query
> >
> > Hello everyone. I hope everyone is doing well.
> >
> > I need some help showing data in a crosstab query. The crosstab shows
> > Agent Name as the row heading and Months as the column heading and it
> > counts applications. When I run the query only the agents with
> > applications. It doesn't show those with a zero value. How do I get
> > all of the agent names to show even if the sales equals zero?
> >
> >
> > Thanks.
> > Karen
>
> ------------------------------------
> Posted by: Duane Hookom <duanehookom@hotmail.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>
>
>
>
> ------------------------------------
> Posted by: John Viescas <johnv@msn.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
> <*> To visit your group on the web, go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/
>
> <*> Your email settings:
> Individual Email | Traditional
>
> <*> To change settings online go to:
> http://groups.yahoo.com/group/MS_Access_Professionals/join
> (Yahoo! ID required)
>
> <*> To change settings via email:
> MS_Access_Professionals-digest@yahoogroups.com
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
> <*> To unsubscribe from this group, send an email to:
> MS_Access_Professionals-unsubscribe@yahoogroups.com
>
> <*> Your use of Yahoo Groups is subject to:
> https://info.yahoo.com/legal/us/yahoo/utos/terms/
>
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (14) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar