New and Not Sure Where to Start

[webedz]

I have taken over a database (really rusty on my Access) and they want me to
try and pull all of their survey information together. Problem is that there
are two tables for this survey. One table contains 27 columns of which 1 is
the "Company", 2 is "User_id" (all null by the way), 3 "Last_name", 4 "Type"
and the remaining columns are all the survey questions with row headers of
"1" -"23". The survey questions were responded to by placing a 1, 2, 3, 4 or
5 in the select column/row.

My second table has 1 column called "Values" which contains 5, 4, 3, 2, 1,
0. That is it.

First the responses of 1-5 should denote Strongly agree (5) down to Not
Applicable (0).

How on earth do I go about getting this somewhat clean - by splitting the
table and re-organizing or is there a way to just write a decent form to pull
this information? I had hoped to provide the following:

Company
Last Name (person being evaluated within the company)
Questions (1-23 in an outline format)
Strongly Agree Agree Neutral Disagree Strongly Disagree Not
Applicable
% % % % %
%

Percentages would be sum of all "5" divided by Total of all responses for
that question by group above.

Thank you all for any insights or suggestions!

 

[KARL DEWEY]

Have two fields in the Values table.

Values Choice
5 Strongly Agree
4 Agree
3 Neutral
2 Disagree
1 Strongly Disagree
0 Not Applicable

Create a union query using all the fields - here it has only 6 of them. I
named it webedz_1 but if you name it different then edit the SQL for the
crosstab query below.
SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[1] = Values.Values
UNION ALL SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[2] = Values.Values
UNION ALL SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[3] = Values.Values
UNION ALL SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[4] = Values.Values
UNION ALL SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[5] = Values.Values
UNION ALL SELECT webedz.Last_name, webedz.Type, Values.Choice
FROM webedz INNER JOIN [Values] ON webedz.[6] = Values.Values;


TRANSFORM Count([Choice])/[Total Of Choice] AS Expr1
SELECT webedz_1.Last_name, Count(webedz_1.Choice) AS [Total Of Choice]
FROM webedz_1
GROUP BY webedz_1.Last_name
PIVOT webedz_1.Choice In ("Strongly Agree", "Agree", "Neutral", "Disagree",
"Strongly Disagree", "Not Applicable");

 

[webedz]

Thanks Karl,

I'll work on it.

Chandra