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;
Posted by: k2j1203@yahoo.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar