Fasten your seat belts, this is gonna get ugly.
As I see it, the only way to get your data to be queryable is to
normalize your table structure. What you really need is your data to
be in a format like this:
DocID, QuestionID, Answer
Without writing zillions of individual queries, this is a pain. So
write some code to do it for you. (If you can't understand code, this
*might* be a problem, but it's either use code or do it manually...)
So, here's what I came up with:
'---BEGIN CODE-----
Option Compare Database
Option Explicit
Public Sub NormalizeTable(ByVal strSrcTable As String)
'--Sample Call: NormalizeTable "MyTable"
'--ASSUMES you have a table called "tblSurvey", with 3 fields:
'--DocID - Long Integer, primary key(1)
'--QuestionNo - Long Integer, primary key (2)
'--Answer - Long Integer, the number you were storing in the Q(n)
field.
Dim dbs As DAO.Database
Dim tdfSrc As DAO.TableDef
Dim intCounter As Integer
Dim strSQL As String
Set dbs = DBEngine(0)(0)
'--point at the table containing your data
Set tdfSrc = dbs.TableDefs(strSrcTable)
'--loop through the fields in the table, appending the SurveyID
(docNo), QuestionID, and Answer to the Survey table
For intCounter = 1 To tdfSrc.Fields.Count - 1
'Create the SQL statement
strSQL = "INSERT INTO tblSurvey (DocID, Answer, QuestionNo)
SELECT [" & tdfSrc.Name & "].[" & tdfSrc.Fields(0).Name & "], [" &
tdfSrc.Fields(intCounter).Name & "], " & intCounter & " AS Expr1 FROM
tblQs;"
'Execute the SQL statement
dbs.Execute strSQL, dbFailOnError
Next intCounter
Set tdfSrc = Nothing
Set dbs = Nothing
End Sub
'---CODE END----
You have to create the tblSurvey table for this to work. Sorry, didn't
feel like messing with making it properly generic - just wanted to get
it working first!
HTH,
Pieter
