Summarize Answers via a Query

[ND Pard]

I have a table named: "Client Review Table 2"
with a Date field (named: Date_Completed) and
numerous Text fields (named: A1, A2, A3 … A30).

The text fields are answers to questions 1, 2, 3 … 30.

Each text field input is limited via a
Validation Rule: "Y" Or "P" Or "N" Or "NA"

What would the SQL be for the query
if I need the count of the number of each response to
each text field.

Hopefully I'll be able to use the query to
create a report similar to the following:

Example:

Question: Yes No Partial NA Total
A1 5 4 3 1 13
A2 4 2 7 0 13
A3 8 5 0 0 13
…
A30 7 6 0 0 13

Total ### ### ### ### 390

Thanks in Advance.

 

[KARL DEWEY]

You have a spreadsheet and need a relational table.
To get the top portion you need a union query and then a crosstab query.
To get the totals you need to use a report and sum in the footer.
SELECT [Date_Completed], "A1" AS [Question], [A1] AS [Response]
FROM [Client Review Table 2]
UNION ALL SELECT [Date_Completed], "A2" AS [Question], [A2] AS [Response]
FROM [Client Review Table 2]
UNION ALL SELECT [Date_Completed], "A3" AS [Question], [A3] AS [Response]
FROM [Client Review Table 2]
.....
UNION ALL SELECT [Date_Completed], "A30" AS [Question], [A30] AS [Response]
FROM [Client Review Table 2];

Then the crosstab query to feed your report.

 

[ND Pard]

Karl,

Your answer is the 'Cat's Meow' ... thank you so much.

The SQL for the Crosstab query I used after I created Query_1 using your
UNION query tip is as follows:

PARAMETERS [Enter a Starting Date MM/DD/YYYY] DateTime, [Enter and Ending
Date MM/DD/YYYY] DateTime;
TRANSFORM Sum(1) AS Expr1
SELECT Question, Sum(1) AS Total
FROM [Query_1]
WHERE (((Date) Between [Enter a Starting Date MM/DD/YYYY] And [Enter and
Ending Date MM/DD/YYYY]))
GROUP BY Question
PIVOT Response;

Now creating a report will be a simple task.

You Da Man !!! Again ... THANK YOU ... THANK YOU ... THANK YOU.

You have a spreadsheet and need a relational table.
To get the top portion you need a union query and then a crosstab query.
To get the totals you need to use a report and sum in the footer.
SELECT [Date_Completed], "A1" AS [Question], [A1] AS [Response]
FROM [Client Review Table 2]
UNION ALL SELECT [Date_Completed], "A2" AS [Question], [A2] AS [Response]
FROM [Client Review Table 2]
UNION ALL SELECT [Date_Completed], "A3" AS [Question], [A3] AS [Response]
FROM [Client Review Table 2]
....
UNION ALL SELECT [Date_Completed], "A30" AS [Question], [A30] AS [Response]
FROM [Client Review Table 2];

Then the crosstab query to feed your report.
--
KARL DEWEY
Build a little - Test a little

I have a table named: "Client Review Table 2"
with a Date field (named: Date_Completed) and
numerous Text fields (named: A1, A2, A3 … A30).

The text fields are answers to questions 1, 2, 3 … 30.

Each text field input is limited via a
Validation Rule: "Y" Or "P" Or "N" Or "NA"

What would the SQL be for the query
if I need the count of the number of each response to
each text field.

Hopefully I'll be able to use the query to
create a report similar to the following:

Example:

Question: Yes No Partial NA Total
A1 5 4 3 1 13
A2 4 2 7 0 13
A3 8 5 0 0 13
…
A30 7 6 0 0 13

Total ### ### ### ### 390

Thanks in Advance.

 

[ND Pard]

Karl,

After trying your Union queries, I found they worked ONLY if each date was a
Unique Date.

I had to modify your Union query as shown below, then it worked GREAT.

PROBLEM: How to Summarize An Access Database Table Used to Store Responses
to Various Documents

I have a table named: "Client_Review Table 2"
with a Date field (Named: Date) and
numerous Text fields (Named: A01, A02, A03 … D05).

The text fields are answers to questions 1, 2, 3 … 30.

Each text field input is limited via a
Validation Rule: "Y" Or "P" Or "N" OR "NA".

What would the SQL be for the Query(s) to
create a report similar to the following:


Question: Yes No Partial NA Total
A01 5 4 3 1 13
A02 4 2 7 0 13
A03 8 5 0 0 13

D05 7 6 0 0 13

Total ### ### ### ### ###


Solution:

Create the form: frm_Start_n_End_Dates with
text fields: txt_Start_Date and txt_End_Date

Make sure this form is OPEN when Query 1 is opened (run).

You can use Query_3 as the record source for your report.

Query_1 (A Select Query)

SELECT "A01" AS Question, A01 AS Response, Sum(1) AS [Count]
FROM [Client Review Table 2]
WHERE (((Date) Between [Forms]![frm_Start_n_End_Dates]![txt_Start_Date] And
[Forms]![frm_Start_n_End_Dates]![txt_End_Date]))
GROUP BY "A01", A01

Union

SELECT "A02" AS Question, A02 AS Response, Sum(1) AS [Count]
FROM [Client Review Table 2]
WHERE (((Date) Between [Forms]![frm_Start_n_End_Dates]![txt_Start_Date] And
[Forms]![frm_Start_n_End_Dates]![txt_End_Date]))
GROUP BY "A02", A02

Union

…

Union

SELECT "D05" AS Question, D05 AS Response, Sum(1) AS [Count]
FROM [Client Review Table 2]
WHERE (((Date) Between [Forms]![frm_Start_n_End_Dates]![txt_Start_Date] And
[Forms]![frm_Start_n_End_Dates]![txt_End_Date]))
GROUP BY "D05", D05;

Query 2 (A Make-Table Query)

SELECT Question, Response, Sum(Count) AS [Count]
INTO Temp
FROM Client_Review_Table_2_A_Qry
GROUP BY Question, Response;

Query 3 (A Crosstab Query)

TRANSFORM Sum(Query_2.Count) AS Count
SELECT Question, Sum(Query_2.Count) AS Total
FROM Query_2
GROUP BY Question
PIVOT Query_2.Response;