Hi:
I need some help with modifying the query below:
++++++++++++++++++++++++++
++++++++++
++++++++++
++
SELECT tblTest01.Division, [Q1] & [Q2] & [Q3] & [Q4] AS RadioButton, Abs(Sum(Q1 Is Not Null)) AS Q1Count, Abs(Sum(Q2 Is Not Null)) AS Q2Count, Abs(Sum(Q3 Is Not Null)) AS Q3Count, Abs(Sum(Q4 Is Not Null)) AS Q4Count FROM tblTest01 GROUP BY tblTest01.Division, [Q1] & [Q2] & [Q3] & [Q4];
++++++++++++++++++++++++++
++++++++++
++++++++++
++
Background Info:
- I have a table (tblTest) with 5 fields (Division, Q1, Q2, Q3, Q4)
- Currently, this table has roughly 100 records; this record count is based on 10 "survey submissions"... each of them has 10 questions
- If one completes a survey today, all 10 answers from that single individual would be automatically added to the Q1 (Quarter 1 column); if that survey would be completed in April then all 10 answers would be added to Q2. Essentially, for each completed records set (10 questions) all values will be in ONE AND ONLY ONE of the 4 fields (quarters)
- What is a value? Values will range from "0 to 5" (6 values selection from radio buttons).
An example (snapshot) of my table data is the following:
Division Q1 Q2 Q3 Q4
Sales 0
Sales 5
Sales 3
Sales 4
Sales 4
Sales 2
Sales 1
Sales 5
Sales 2
Sales 3
Finance 5
Finance 5
Finance 4
Finance 0
Finance 1
Finance 3
Finance 2
Finance 2
Finance 4
Finance 0
This sample data is based on "Sales" completing the survey in the 1st quarter while "Finance" completed its survey in the 2nd quarter. P.S. Please keep in mind that I just typed the values (0 to 5) into this message... they may not correspond to the following GROUPED COUNTS of my query. Using differnt values won't have an impact on the actual problem I'm listing here.
Now, let's assume that I have even more completed "record sets" of completed surveys. The surveys were taken througout an entire year so I have answers for all 4 quarters. Also, let's assume that each department has chosen either one of the 6 values at least once.
When I now apply the query listed above, I get a "perfect result" from the GROUPED query... meaning, I have 6 records and there is a count in each of the 4 quarters.
The result would look something like this (querying only on "Sales"):
==========================
==========
========
Division RadioButton Q1Count Q2Count Q3Count Q4Count
Sales 0 5 36 2 4
Sales 1 6 7 8 5
Sales 2 4 5 7 5
Sales 3 24 4 5 5
Sales 4 12 2 5 2
Sales 5 5 2 1 35
Now, here's the problem:
================
- If for whatever reason, a survey participant (here "Finance") has not selected value "3" at all, then my query shows only the grouped counts for 5 values instead of 6 values... which makes sense.
Division RadioButton Q1Count Q2Count Q3Count Q4Count
Finance 0 1 0 0 0
Finance 1 1 0 0 0
Finance 2 16 0 0 0
Finance 4 7 0 0 0
Finance 5 3 0 0 0
Okay, here's now my question: For a scenario like "Finance", how can I make sure to display "3" (with 0 for the grouped count) as well? So, I need to come up w/ a query that gives me the following output.
DESIRED OUTPUT:
===========
Division RadioButton Q1Count Q2Count Q3Count Q4Count
Finance 0 1 0 0 0
Finance 1 1 0 0 0
Finance 2 16 0 0 0
Finance 3 0 0 0 0
Finance 4 7 0 0 0
Finance 5 3 0 0 0
My preference would be to just modify the query as listed on top of this message. If that's not possible however, does anyone know of another "smart approach" to show the non-exising value with a "0 count"?
Thanks,
Tom
