Pivot table from Survey Results

  • Thread starter Thread starter Diana Bowe
  • Start date Start date
D

Diana Bowe

I have a database table created from the results of a employee survey. The
rows contains 60 questions and the columns contains 25 departments with the
results in the cells below. I need to create a Pivot table to show the
departments that scored less than 30% for each question. How do I create
the Pivot table


Questions 5 Twins (back of the house) 5 Twins (front of the house) All
Casino All Coral & Beach Towers
1. I am proud to work for Atlantis/Ocean Club 100.00% 100.00% 99.79%
95.74%
2. I am proud of the product and service my department provides 95.00%
100.00% 94.26% 78.75%
3. Atlantis/Ocean Club is an excellent resort to work for compared to
other hotels/resorts in the area 100.00% 100.00% 98.90% 93.16%
4. Atlantis/Ocean Club is the pride of the local community 100.00%
100.00% 96.09% 90.81%
5. Atlantis/Ocean Club contributes to the success of the Bahamas
100.00% 100.00% 99.15% 94.48%
 
Diana,

If your row headers contain the questions, and the columns contain the names
of the 25 departments, you already have a pivot table, and trying to do what
you want will be difficult.

If you had a column for question #, a column for the Department, and a
column for the results, then creating the pivot table would be relatively
simple. Question # as row header, Department as column header, Results as
value).

You could create a query to denormalize your data into this format, or you
could write some code to open the recordset and loop through each record and
each field in each record and write the data into this format. The latter
is probably quicker. Before you execute this code, create the new table
tblDenormalizedResults (QuestionNum, Department, Score)

Something like(put this in a code module or in the code behind a button on a
form). This is untested (air code) but should be pretty close. If your
table has a text value for the question number or the actual text of the
question in that field, it will have to be wrapped in quotes (chr$(34)) just
like the department name.

Dim db as dao.database
dim rs as dao.recordset
Dim fld as dao.field
dim strSQL as string

Set db = currentdb
Set rs = db.openrecordset ("SELECT * FROM YourTable")
While not rs.eof
For each fld in rs.fields
if fld <> 1 then
strSQL = "INSERT INTO tblDenormalizedResults " _
& "Values (QuestionNum, Department, Score) " _
& "SELECT " & rs("QuestionNum") & ", " _
& chr$(34) & fld.Name &
chr$(34) & ", " _
& rs(fld.Name)
db.execute strSQL
endif
Next
rs.movenext
Wend
rs.close
set rs = nothing
set db = nothing

HTH
Dale
 
Back
Top