Use this technique to switch fields to records.
Access Archon #137
Introduction
Ray Wright wrote to me to ask how he could convert a table with over 100 questionnaire fields to a more manageable format, with the fields converted to records in a table, to make it easier to tabulate the data. It is indeed difficult and unwieldy (if not impossible) to create a crosstab query with so many fields, so I decided to devote an article to the technique I use to switch fields to records.
Preparations
The tblSurvey table (part of which is shown in Figure A) has the raw data from the questionnaires. It has 44 fields (cut down from the original table, which had over 100 fields). There is a Text field, ID, which is the key field, and the other fields are either Boolean or Text, with the Text fields taking a numeric value from 1 to 5.
Figure A. The table with raw survey data in numerous fields. Click image to see full size version.
To switch the fields to records, I first created a table (with the prefix zstbl to indicate that it is a system table) with just three fields: SurveyID, a Long Integer field indexed Yes (Duplicates OK), Question and Answer (both text fields). This table is copied to create a results table that is filled from code.
The CreateResultsTable function fills a results table with records containing field names and values from the original tblSurvey. For convenience, the function can be run from the macro mcrCreateResultsTable. After this is done, then a simple totals query (qtotAnswers) totals the number of Yes, No, and 1 through 5 answers for each question. This query is shown in Figure B.
Figure B. A Totals query giving the number of each answer for each question. Click image to see full size version.
VBA Code
Public Function CreateResultsTable()
On Error Resume Next
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim flds As DAO.Fields
Dim rstSource As DAO.Recordset
Dim rstTarget As DAO.Recordset
Dim strPrompt As String
Dim strResultsTable As String
Dim strSourceTable As String
Dim strTableTemplate As String
Dim strTitle As String
‘If there is already a results table for today, delete it
strTableTemplate = “zstblSurveyResults”
‘Create a table name including today’s date
strResultsTable = “tblSurveyResults_” & Format(Date, “dd-mmm-yyyy”)
Debug.Print “New table name: ” & strResultsTable
DoCmd.DeleteObject acTable, strResultsTable
On Error GoTo ErrorHandler
‘Make copy of table template
DoCmd.CopyObject newname:=strResultsTable, _
sourceobjecttype:=acTable, _
sourceobjectname:=strTableTemplate
‘Fill results table with data from source table
strSourceTable = “tblSurvey”
Set dbs = CurrentDb
Set rstSource = dbs.OpenRecordset(strSourceTable)
Set rstTarget = dbs.OpenRecordset(strResultsTable)
Do While Not rstSource.EOF
‘Set a variable to the Fields collection of the source table’s recordset
Set flds = rstSource.Fields
For Each fld In flds
‘Create a set of records in the target table corresponding to
‘one record in the source table
Debug.Print “Field name: ” & fld.Name
rstTarget.AddNew
‘Skip first field (the ID field), and get names and values from the other fields
If fld.Name <> “ID” Then
rstTarget![SurveyID] = rstSource![ID]
rstTarget![Question] = fld.Name
If fld.Type = dbBoolean Then
‘Change Boolean answers to “Yes” or “No” text
rstTarget![Answer] = IIf(fld.Value = True, “Yes”, “No”)
Else
‘For Text answers, just store the value as is
rstTarget![Answer] = fld.Value
End If
rstTarget.Update
End If
Next fld
rstSource.MoveNext
Loop
rstSource.Close
strTitle = “Finished”
strPrompt = strResultsTable & ” results table created”
MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
ErrorHandlerExit:
Exit Function
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Function
References
The code in the sample database needs the following references (in addition to the default references):
Microsoft DAO 3.6 Object Library
Microsoft Scripting Runtime
If you import code or objects into a database of your own, you may need to set one or more of these references. The version number may differ, depending on your Office version; check the version you have. References are set in the References dialog, opened from the VBA window. For more information on working with references, see Access Archon #107, Working with References.
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch137.zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Fields to Records.mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
