By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Loop through Field names in code???

P: n/a
Mal
Hello.
I have inherited a badly designed database and am trying to improve
it.
It is basically a flat file for survey results.

one table holds, Name, Age etc as well as a field for each question.
I have created

tblResponders
Name etc

tblQuestions
IdNum
Question Text

tblResponses
IdNum
ResponderID
QuestionID
Response
I think the best way to clean things up now is to...
*loop through the Current Table
* Take one record at a time and then loop the fields
* build an append statement for each field matching the field name
to the question ID in the Question Table
* insert the Responder ID, Question ID and response to tblResponses

So... How do I do that looping....ie refer to the field names
I could even build a case statement.
eg.. Select Case <<SomeFieldName>>
Case "FirstImpression
MyQuestionID = 1
case "StaffFriendly"
MyQuestionID = 2
etc...

ideas? Thoughts?

TIA
Mal
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You can loop through the Fields collection of the TableDef.

Example:
http://members.iinet.net.au/~allenbrowne/func-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mal" <ma******@hotmail.com> wrote in message
news:75**************************@posting.google.c om...
Hello.
I have inherited a badly designed database and am trying to improve
it.
It is basically a flat file for survey results.

one table holds, Name, Age etc as well as a field for each question.
I have created

tblResponders
Name etc

tblQuestions
IdNum
Question Text

tblResponses
IdNum
ResponderID
QuestionID
Response
I think the best way to clean things up now is to...
*loop through the Current Table
* Take one record at a time and then loop the fields
* build an append statement for each field matching the field name
to the question ID in the Question Table
* insert the Responder ID, Question ID and response to tblResponses

So... How do I do that looping....ie refer to the field names
I could even build a case statement.
eg.. Select Case <<SomeFieldName>>
Case "FirstImpression
MyQuestionID = 1
case "StaffFriendly"
MyQuestionID = 2
etc...

ideas? Thoughts?

TIA
Mal

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.