Query values without specifying field names?

[kathyburke40]

Odd problem. I have a table in the following format:

DocID Question1 Question2 Question3
------------------------------------------------
298 1, 2, 3 or 0

Each Question field will have 1,2,3 or 0 if not completed. The number
of Question fields will vary.

I need to count all the questions, then figure out how many have 0
answers. Is there any way to query values dynamically when the field
names change. I'm trying to avoid writing multiple queries, each with
hundreds of field names.

Thanks for any help provided...I'm probably out in left field.

Kat

[Chuck Grimsby]

I'm not sure if I understand your question or not.
Do you have 1 table, or many tables?
If there's just the one table, you should know how many fields it
has....

[Alan Webb]

Kathy Burke,
This is a lot easier if you have something like:

Doc_tbl
---->Question_tbl

so that question table is the many side of a one-to-many relationship with
the Doc_tbl. I'd be tempted to quietly write some code that iterated
through the fields looking for zeros and building a temporary table listing
the questions with zeros for a given doc_tbl record.

--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS"

<ka**********@c omcast.net> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.com...

Odd problem. I have a table in the following format:

DocID Question1 Question2 Question3
------------------------------------------------
298 1, 2, 3 or 0

Each Question field will have 1,2,3 or 0 if not completed. The number
of Question fields will vary.

I need to count all the questions, then figure out how many have 0
answers. Is there any way to query values dynamically when the field
names change. I'm trying to avoid writing multiple queries, each with
hundreds of field names.

Thanks for any help provided...I'm probably out in left field.

Kat

[pietlinden]

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(s trSrcTable)

'--loop through the fields in the table, appending the SurveyID
(docNo), QuestionID, and Answer to the Survey table

For intCounter = 1 To tdfSrc.Fields.C ount - 1
'Create the SQL statement
strSQL = "INSERT INTO tblSurvey (DocID, Answer, QuestionNo)
SELECT [" & tdfSrc.Name & "].[" & tdfSrc.Fields(0 ).Name & "], [" &
tdfSrc.Fields(i ntCounter).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