ACCESS FEED: Re: [MS_AccessPros] Showing all names in crosstab query

Minggu, 29 Maret 2015

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