By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,090 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Crosstab Query to display all possibilities, even if zero

P: 20
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:

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

Q:Claims_Assign_Year_Adj vs. Type SQL:

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

Q:Claims_Assign_Year_Type vs. Adj SQL:

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 23 '07 #1
Share this Question
Share on Google+
1 Reply


nico5038
Expert 2.5K+
P: 3,072
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)
Mar 24 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.