Crosstab Query to display all possibilities, even if zero

[bruce24444]

First of all I'm new to the forum and am working on my first database. So far I think I've done not too bad but have hit a stumbling block for which I'm not sure how to get around.

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. Tables are as follows “Staff” “Loss_Type” and “Claims_Assignment”.

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

From this 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 an error about ambiguous joins.

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

What I 'd like to see is "Q:Claims_Assign_Year_Adj vs. Type" display all possible “Staff” vs. all possible “Loss_Type” even if results equal zero for one or both variables.

From reading different info I assume I'm to use a NZ() function, but not sure how or if this is the approach required.




Q:Claims_Assign_Year SQL:

Code:
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];



Q:Claims_Assign_Year_Adj vs. Type SQL:

Code:
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];



Q:Claims_Assign_Year_Type vs. Adj SQL:

Code:
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];

 

[dhookom]

I would first create a cartesian query of Loss_Type and Staff:
[red]SELECT Loss_Type.[Type Of Loss], Staff.[Last Name], Staff.[First Name]
FROM Loss_Type, Staff;
[/red]
Then create a crosstab based on this query and your Claim_Assignment table. Set the Joins to include all records from your cartesian query. Use the fields from your cartesian query as your row and column headers.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]