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

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

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:

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:

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

[nico5038]

Assuming you have a tblStaff and a tblLossType, you can create a select query like:

select StaffID, LosType from tblStaff, tblLossType

This will force all combinations to appear.
This query can be used with the other table in an OUTER (Left or Right) join to create your crosstable query with all fields filled.

Getting the idea ?

Nic;o)