Crosstab Query to Display Zero

[bruce24444]

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_Assignm ent”.

From this “Claim_Assignme nt” 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_Assig n_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

[MMcCarthy]

Can you post the SQL of the two queries you are currently using as this would make things a lot clearer.

Mary

[bruce24444]

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/Access/Explanation.htm

I hope this helps

[MMcCarthy]

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

[bruce24444]

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.

SELECT Staff.[Last Name], Staff.[First Name], Loss_Type.[Type of Loss], Claim_Assignment.[Assigned Date]
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]
WHERE (((Claim_Assignment.[Assigned Date]) Between [Start_End]![Year Start Date] And [Start_End]![Year End Date]))
ORDER BY Staff.[Last Name];

TRANSFORM Count([Q:Claims_Assign_Year].[Assigned Date]) AS [The Value]
SELECT Staff.[Last Name], Staff.[First Name], Count([Q:Claims_Assign_Year].[Assigned Date]) AS [Total Of Assigned Date]
FROM [Q:Claims_Assign_Year] RIGHT JOIN Staff ON [Q:Claims_Assign_Year].[Last Name] = Staff.[Last Name]
GROUP BY Staff.[Last Name], Staff.[First Name]
PIVOT [Q:Claims_Assign_Year].[Type of Loss];

TRANSFORM Count([Q:Claims_Assign_Year].[Assigned Date]) AS [The Value]
SELECT Loss_Type.[Type of Loss], Count([Q:Claims_Assign_Year].[Assigned Date]) AS [Total Of Assigned Date]
FROM [Q:Claims_Assign_Year] RIGHT JOIN Loss_Type ON [Q:Claims_Assign_Year].[Type of Loss] = Loss_Type.[Type of Loss]
GROUP BY Loss_Type.[Type of Loss]
PIVOT [Q:Claims_Assign_Year].[Last Name];

[NeoPa]

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.