OUCH. Too bad your table design is wrong. If you ever do this again I
suggest you take a look at Duane Hookom's At Your Survey
Duane Hookom has a sample survey database "At Your Survey" at
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=4
This fully functional application uses a small collection of tables, queries,
forms, reports, and code to manage multiple surveys. Users can create a
survey, define questions, enter pre-defined answers, limit to list, report
results, create crosstabs, and other features without changing the design of
any objects.
As to the problem working with the current data structure, I am stuck. What I
would probably do is create the normalized table structure and then use append
queries to get the data into the proper format. TEDIOUS work.
I do have an untested VBA routine that might work for you. You would need to
build a table with fields like the following:
RecordID (the primary key of your existing table)
<<additional field that are not questions in the survey)
QuestionName (this will hold the field name)
Response (This will hold the value in the field)
Then your output query would be
SELECT QuestionName, Response, Count(RecordID)
FROM TheNewTable
GROUP BY QuestionName, Response
The VBA routine is as follows (watch out for line wrapping causing syntax errors)
Good Luck
'======================================================================
'Turn non-normalized data (repeating fields) into a normalized table
Public Function fMakeNormalizedTable(strSource, strDestination _
, intCountIdColumns _
, Optional intStartField = 0, Optional intStopField = 0 _
, Optional intGroupSize = 1 _
, Optional tfIncludeNulls As Boolean = False)
'===============================================================================
' Procedure : fMakeNormalizedTable
' DateTime : 5/11/2006 07:39
' Author : John Spencer
' Purpose : Take a non-normalized table with repeating columns and normalize
' the table. Source table structure is expected to be one to n identifier
'columns
' followed by many repeating columns. For example
' FirstName LastName WorkPhone HomePhone MobilePhone FaxPhone AlternatePhone
' Destination table should already exist and should have a structure similar 'to
' the source table. The structure would be something like
' the Identifier fields, a field to hold the source's field name, and a field 'to
' hold the data in the repeating fields. For example
' FirstName LastName PhoneType PhoneNumber
'------------------------------------------------------------------------------
' strSource = Name of table with data
' strDestination = Name of destination table
' intCountIdColumns = number of identifier columns
' intStopField = Last Column to be used in building populating destination 'table
' intStartField = First repeating column
' intGroupSize = Allows for regular group size (x columns in each group)
' # Gum Sold, Flavor
' # Chiclets, ChicletFlavor; #Wrigley, WrigleyFlavor
' tfIncludeNulls = If True then make records for fields where the value is 'null
'===============================================================================
Dim dbAny As DAO.Database
Dim strSqlBase As String, strSql As String, strSQLTarget As String
Dim strBuildTableSQL As String
Dim intLoop As Integer
Dim strFieldName As String
Dim rstAny As DAO.Recordset
Dim intLoop2 As Integer
Dim strAdd As String
Static iErrCount As Integer
On Error GoTo ERROR_fMakeNormalizedTable
'---------------------------------------------------------------
' Future Code Enhancements:
' -- Add ability to skip keyfield column in destination table
' -- add ability to start at any column in source table
'---------------------------------------------------------------
Set dbAny = CurrentDb()
'------------------------------------------------------------------------------
' Determine number of times to loop
'------------------------------------------------------------------------------
iErrCount = 1 'set ierrCount to force stop
If intStopField = 0 Or intStopField >
dbAny.TableDefs(strSource).Fields.Count Then
intStopField = dbAny.TableDefs(strSource).Fields.Count - 1
Else
intStopField = intStopField - 1
End If
If intStartField > intStopField Then
MsgBox "Stop! Start field is after stop field.", , "Please fix"
Exit Function
End If
If intStartField = 0 Or intStartField < intCountIdColumns Then
intStartField = intCountIdColumns
Else
intStartField = intStartField - 1
End If
'Check numbers to make sure they work
If intGroupSize <> 1 Then
If (1 + intStopField - intStartField) Mod intGroupSize <> 0 Then
'adjust intstopfield down
intStopField = intStopField - _
(1 + intStopField - intStartField) Mod intGroupSize <> 0
End If
End If
'------------------------------------------------------------------------------
' Get field names in destination Table and build insert statement
'------------------------------------------------------------------------------
iErrCount = 0 'initialize errCount
With dbAny.TableDefs(strDestination) 'if this errors then attempt to
'build table
For intLoop = 0 To .Fields.Count - 1
strSQLTarget = strSQLTarget & ", [" & .Fields(intLoop).name & "]"
Next intLoop
End With 'dbAny.TableDefs(strDestination)
strSQLTarget = Mid(strSQLTarget, 3) 'Strip off beginning ", "
strSQLTarget = "INSERT INTO [" & strDestination & "] (" & _
strSQLTarget & ") "
'Build SELECT clause for SELECT query portion of Insert query
'Add Identifier fields
With dbAny.TableDefs(strSource)
If .Fields.Count < intCountIdColumns + 1 Then
MsgBox "Not enough fields in destination table", , "Sorry"
Exit Function
End If
strAdd = vbNullString
For intLoop = 0 To intCountIdColumns - 1
strAdd = strAdd & ", [" & .Fields(intLoop).name & "]"
Next intLoop
strSqlBase = "SELECT " & Mid(strAdd, 3) 'Strip off beginning ", "
'Populate the table
For intLoop = intStartField To intStopField Step intGroupSize
strSql = vbNullString
strAdd = vbNullString
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & ", """ & strFieldName & """, " & _
"[" & strFieldName & "] "
Next intLoop2
strSql = strAdd & " FROM [" & strSource & "] "
strAdd = vbNullString
If tfIncludeNulls = False Then
'Build where clause if nulls are to be excluded
For intLoop2 = 0 To intGroupSize - 1
strFieldName = .Fields(intLoop + intLoop2).name
strAdd = strAdd & "[" & strFieldName & "] is not Null OR "
Next intLoop2
strSql = strSql & " WHERE " & Left(strAdd, Len(strAdd) - 4) 'Strip
'off last Or
End If
strSql = strSQLTarget & " " & strSqlBase & " " & strSql
dbAny.Execute strSql, dbFailOnError
Next intLoop
End With
EXIT_fMakeNormalizedTable:
On Error GoTo 0
Exit Function
ERROR_fMakeNormalizedTable:
If Err.Number = 3265 And iErrCount = 0 Then
iErrCount = iErrCount + 1
'------------------------------------------------------------------------------
' Build the destination table based on the source table
'------------------------------------------------------------------------------
'Identifier fields
With dbAny.TableDefs(strSource)
For intLoop = 0 To intCountIdColumns - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " " & _
fGetFieldTypeName(.Fields(intLoop).Type)
Next intLoop
'Repeating value fields
For intLoop = intStartField To intStartField + intGroupSize - 1
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & " Text(64)"
strBuildTableSQL = strBuildTableSQL & ", " & _
.Fields(intLoop).name & "Value " & _
fGetFieldTypeName(.Fields(intStartField).Type)
Next intLoop
strBuildTableSQL = Mid(strBuildTableSQL, 3)
strBuildTableSQL = "Create Table " & strDestination & _
"( " & strBuildTableSQL & ")"
dbAny.Execute strBuildTableSQL, dbFailOnError
End With
dbAny.TableDefs.Refresh
Resume
Else
MsgBox "Error " & Err.Number & vbCrLf & Err.Description & vbCrLf & _
" in procedure fMakeNormalizedTable"
Err.Clear
End If
Stop: Resume 'Debug purposes only. Remove from final code
End Function
Private Function fGetFieldTypeName(fldAnyType) As String
'returns string field type
Dim strAny As String
Select Case fldAnyType
' Case dbBigInt
' strAny = "Big Integer"
Case dbBinary
strAny = "Binary"
Case dbBoolean
strAny = "Boolean"
Case dbByte
strAny = "Byte"
' Case dbChar
' strAny = "Char"
Case dbCurrency
strAny = "Currency"
Case dbDate
strAny = "DateTime"
Case dbDecimal
strAny = "Decimal"
Case dbDouble
strAny = "Double"
Case dbFloat
strAny = "Double"
Case dbGUID
strAny = "GUID"
Case dbInteger
strAny = "Integer"
Case dbLong
strAny = "Long"
' Case dbLongBinary
' strAny = "Long Binary (OLE Object)"
Case dbMemo
strAny = "Memo"
Case dbNumeric
strAny = "Numeric"
Case dbSingle
strAny = "Single"
Case dbText
strAny = "Text"
Case dbTime
strAny = "Time"
' Case dbTimeStamp
' strAny = "Time Stamp"
' Case dbVarBinary
' strAny = "VarBinary"
' Case Else
' strAny = "Unknown Type"
End Select
fGetFieldTypeName = strAny
End Function
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Just because MS wants to stop using Usenet doesn't mean we have to.
Anyway, I'm trying to figure out a way to export survey results from
an Acc2k3 db to Excel for number crunching. I have a BUNCH of columns
(227 fields broken into 7 sections, representing each question of the
survey) with the possible answers for each one being Yes, No, or N/A.
I need to count the number of each response for each question. A
simple crosstab ain't gonna do it. I could do individual IIf's for
reach response for each question but that would literally triple my
column count (and amount of effort) in the queries.
In Access the fields are at the top and the data below. In Excel, the
fields will be at the left with the data to the right. I know that
doesn't really matter but it's part of what has me going through
changes trying to figure out how to do this.
So, for each field/column, I need the total count (or sum if that
works) for each answer.
Any ideas?
TIA,
RD
