Didn't notice the calendar table. I expected the cartesians need to intervene to make sure all the agents were accounted for.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Thu, 28 Aug 2014 18:07:13 +0200
Subject: Re: [MS_AccessPros] Showing all values in a table for a crosstab query
Duane-
It sounds like she already has that in her [Calendar Table]. I didn't think of doing a Cartesian Product, but that's another way to solve it.
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)
I start by creating a table [tblAllDates] of all dates with a single date field [TheDate] and add every possible date I will ever need. You can then create a cartesian query that selects every possible TheDate and AgentID
SELECT TheDate, AgentID
FROM tblAllDates, tblAgents
Then use this with your Enrollment table joining on the date and agent. Make sure the join is set to display every record from the cartesian query.
BTW if you want to use this as the record source for a report you should consider using a method that create "relative" dates so your report fields never change.
Duane Hookom MVP
MS Access
To:
MS_Access_Professionals@yahoogroups.comFrom:
MS_Access_Professionals@yahoogroups.comDate: Thu, 28 Aug 2014 08:20:23 -0700
Subject: Re: [MS_AccessPros] Showing all values in a table for a crosstab query
Thanks for the help. Unfortunately, I am trying to get all agents and all dates. For example,
| 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)
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 dates
SELECT 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 agents
SELECT Count([All Enrollments].HICN) AS CountOfHICN, [lkupCCAI Agents].[Agent Name]
FROM [lkupCCAI Agents] LEFT JOIN [All Enrollments] ON [lkupCCAI Agents].ID = [All Enrollments].Rep_ID
GROUP 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
Tidak ada komentar:
Posting Komentar