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
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On Mar 28, 2015, at 3:59 PM, k2j1203@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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.
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
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
---In MS_Access_Professionals@yahoogroups.com, <wrmosca@...> wrote :
Duane
Well, it certainly is worth trying.
-Bill
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
---In 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
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 10 Mar 2015 10:57:31 -0700
Subject: RE: [MS_AccessPros] Showing all names in crosstab query
Duane
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 10 Mar 2015 10:57:31 -0700
Subject: RE: [MS_AccessPros] Showing all names in crosstab query
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
Microsoft Office Access MVP
My nothing-to-do-with-Access blog
---In 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
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 10 Mar 2015 09:59:18 -0700
Subject: RE: [MS_AccessPros] Showing all names in crosstab query
Sorry. Here's the sql query.
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
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
From: MS_Access_Professionals@yahoogroups.com
Date: Tue, 10 Mar 2015 09:59:18 -0700
Subject: RE: [MS_AccessPros] Showing all names in crosstab query
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, <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
From: 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
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
From: 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: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (11) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar