By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

Crosstab Query to Display Zero

P: 20
What I have is a database which tracks assigned files to a certain people which is generated by a form and then recorded into a table. Table are as follows “Staff” “Loss_Type” and “Claims_Assignment”.

From this “Claim_Assignment” table I have created separate queries that provide reports sorted by certain predetermined dates. IE: week, month, year


I have designed (2) crosstab queries from the “Q:Claims_Assign_Year” to return results from which I want to create report. One crosstab query is staff vs type and the other is type vs staff.

What I would like is (1) query that displays all 26 “Staff” vs All 22 “Types” in one report.

What was happening was when I joined “Staff” from the table and “Staff” from the Query Summary all the staff would be listed.

As soon as I joined “Loss_Types” from the table to the Yearly Summary Query, I receive a error aobut ambigious joins.

As a result I created (2) queries so that I could not get (1) query to display all 26 “Staff” vs All 22 “Types” in one report.

Can I get one query to display all possible staff vs all possible loss types even if results equal zero for both variables
Mar 22 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Can you post the SQL of the two queries you are currently using as this would make things a lot clearer.

Mary
Mar 22 '07 #2

P: 20
I have to apologize for my ignorance, but this is the first database I've designed and couldn't figure out how to post the screen shots so I created a webpage for illustration purposes.

http://www.geocities.com/bruce24444/...xplanation.htm

I hope this helps
Mar 22 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Open the query in Access Design View.

Now change the view to SQL. You will see the icon on the toolbar, it will look like a blue triangle. Click on the arrow to the right of the icon and change view to SQL. Now just copy and paste the code in here.

Mary
Mar 22 '07 #4

P: 20
I hope this is what you were asking for. Again, this is the first time I've used Access, so it's a learning process as well as to terminology.

Expand|Select|Wrap|Line Numbers
  1. SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
  2. FROM Start_End, Loss_Type INNER JOIN (Staff INNER JOIN Claim_Assignment ON Staff.[Last Name] = Claim_Assignment.[Last Name]) ON Loss_Type.[Type of Loss] = Claim_Assignment.[Type of Loss]
  3. WHERE (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Year Start Date] And [Start_End]![Year End Date]))
  4. ORDER BY Staff.[Last Name];
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count([Q:Claims_Assign_Year].[Assigned Date]) AS [The Value]
  2. SELECT Staff.[Last Name], Staff.[First Name], Count([Q:Claims_Assign_Year].[Assigned Date]) AS [Total Of Assigned Date]
  3. FROM [Q:Claims_Assign_Year] RIGHT JOIN Staff ON [Q:Claims_Assign_Year].[Last Name] = Staff.[Last Name]
  4. GROUP BY Staff.[Last Name], Staff.[First Name]
  5. PIVOT [Q:Claims_Assign_Year].[Type of Loss];
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count([Q:Claims_Assign_Year].[Assigned Date]) AS [The Value]
  2. SELECT Loss_Type.[Type of Loss], Count([Q:Claims_Assign_Year].[Assigned Date]) AS [Total Of Assigned Date]
  3. FROM [Q:Claims_Assign_Year] RIGHT JOIN Loss_Type ON [Q:Claims_Assign_Year].[Type of Loss] = Loss_Type.[Type of Loss]
  4. GROUP BY Loss_Type.[Type of Loss]
  5. PIVOT [Q:Claims_Assign_Year].[Last Name];
Mar 22 '07 #5

NeoPa
Expert Mod 15k+
P: 31,494
That's a fine job :)
Looking at your picture posted, the JOINs you're using (for the last query) are not supported. You can't do an OUTER JOIN from two separate tables into a single table.
Mar 23 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.