Karen-
That's what you should get when you use the two queries I suggested. Did you try them?
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 Aug 28, 2014, at 5:20 PM, k2j1203@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Thanks for the help. Unfortunately, I am trying to get all agents and all dates. For example,
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
| 8/1/2014 | 8/2/2014 | 8/3/2014 | |
| John | 1 | 1 | |
| George | 1 | ||
| Michael |
Where the intersection of Michael and 8/2 will still show up even though neither the column nor the row have data.
Karen
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Karen-
Don't bother to do the counting in your underlying queries.
qryAllDates:
SELECT [Calendar Table].[Date], [All Enrollments].HICN, [All Enrollments].Rep_ID
FROM [Calendar Table] LEFT JOIN [All Enrollments]
ON [Calendar Table].Date = [All Enrollments].Application_Date;
Crosstab:
TRANSFORM COUNT(qryAllDates.HICN) As MbrCount
SELECT [lkupCCAI Agents].[Agent Name]
FROM [lkupCCAI Agents]
LEFT JOIN qryAllDates ON [lkupCCAI Agents].ID = qryAllDates.Rep_ID
GROUP BY [lkupCCAI Agents].[Agent Name]
PIVOT qryallDates.[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 Aug 28, 2014, at 4:16 PM, k2j1203@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hello everyone. I hope everyone is well.
I have a bit of a puzzler, well it's one for me. I am trying to run a crosstab query and can't seem to figure out how to get the results that I want. Here's what I have.
I have a table with membership enrollment data and another table of agent names. The Enrollment table has fields MemberID, AgentID, and AppDate. The Agents table has fields AgentID and AgentName. The result that I want to get is to have all of the agent names as row headings and AppDate as coloumn headings with the value being Count of MemberID. I need to show every date in a month whether there is a value in the Count MemberID or not. I created a query that gives me these results. I also want to have all of the agent names whether there is a value in Count of MemberID. I am able to run a query that gives me these results as well. Here is where my problem comes in. I can't seem to get a crosstab query to show all of the results from both queries when I link them (which I do using AgentID). I can get all of the dates as column headings but then I lose a list of all of the agents. Only agents with AppDate values show. When I modify it show all of the rep names, I don't see all of the dates in the month. To throw another monkey wrench in, I need pull information for a certain month. So giving the criteria a date range, also only gives me the dates and agent names that aren't null.
I hope that I explained my situation well so that you can understand. Below the sql queries that I wrote.
Query to show all datesSELECT Count([All Enrollments].HICN) AS CountOfHICN, [Calendar Table].Date
FROM [Calendar Table] LEFT JOIN [All Enrollments] ON [Calendar Table].Date = [All Enrollments].Application_Date
GROUP BY [Calendar Table].Date;
Query that shows all of the agentsSELECT Count([All Enrollments].HICN) AS CountOfHICN, [lkupCCAI Agents].[Agent Name]FROM [lkupCCAI Agents] LEFT JOIN [All Enrollments] ON [lkupCCAI Agents].ID = [All Enrollments].Rep_IDGROUP BY [lkupCCAI Agents].[Agent Name];I really appreciate any help that you can give me - even if it's to tell me that I can't get what I want.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 (6) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar