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. 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:
Expand|Select|Wrap|Line Numbers
- 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:
Expand|Select|Wrap|Line Numbers
- 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:
Expand|Select|Wrap|Line Numbers
- 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];
