ACCESS FEED: RE: [MS_AccessPros] Showing all values in a table for a crosstab query

Kamis, 28 Agustus 2014

RE: [MS_AccessPros] Showing all values in a table for a crosstab query

Duane, you are a blessing from above. THANK YOU SO MUCH!!!! You have no idea how much time you have saved me by creating this query!!

You guys are the best!

Karen

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

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.com
From: MS_Access_Professionals@yahoogroups.com
Date: 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

http://www.viescas.com/

(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 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