472,141 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,141 software developers and data experts.

Crosstab Query to display all possibilities, even if zero

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
1 3541
3,080 Expert 2GB
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 ?

Mar 24 '07 #2

Post your reply

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

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by deejayquai | last post: by
6 posts views Thread by tizmagik | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.