469,612 Members | 1,674 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,612 developers. It's quick & easy.

Crosstab Query to Display Zero

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
5 2623
14,534 Expert Mod 8TB
Can you post the SQL of the two queries you are currently using as this would make things a lot clearer.

Mar 22 '07 #2
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.


I hope this helps
Mar 22 '07 #3
14,534 Expert Mod 8TB
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.

Mar 22 '07 #4
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
32,200 Expert Mod 16PB
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.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
12 posts views Thread by jkearns | last post: by
2 posts views Thread by deejayquai | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.