Insert values into table1 based on table2

[SaviorSix]

Hello.

I made this database, based on Duane Hookum's "At Your Survey" design.
It was made for medical survey-questionnaire responses. It is attached with some sample data so you can see what I am talking about more easily


One of the tables, "questionTable", lists all of the questions (text, type of question, default response, etc), and the survey each question belongs to.

For questions that have a pre-defined list of answers, a relationship exists between questionTable, and responseListTable.

They are joined (1:m) by QID - and autonumber pk of questionTable.

I have a form "SurveyDesignForm", which is used to create each survey, the questions for that survey, and the response lists for each appropriate question. For each question on the form, you can click the "View" label to make entries to the "responseListTable", based on that QID.


My dillema is this:
There are often MANY questions that require a predefined response of
0 No
1 Yes
99 Unknown/Missing

Rather than manually entering, or copying and pasting this, for every question, is there a way to automate entering those items into the responseListTable for each question with a 'responseType' of 'YesNo'? (You will see what I mean when you look at 'SurveyDesignForm')

I was thinking a loop with a SQL insert maybe? But I am not sure of the syntax needed for that...

any insight is appreciated

 

[jal]

I think you are saying you want to insert three records into the ResponseList table. That would be 3 insert commands.


Let's say your QID is 42. Do something like this for your first query:

DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 1, 'Yes')"

And the other two queries would be simialar

DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 0, 'No')"


DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 99, 'Unknown/Missing')"

I'm not sure whether you need a loop because I haven't spent enough time trying to figure out whether you need to bulk-insert or just do one a time.

 

[SaviorSix]

I think you are saying you want to insert three records into the ResponseList table. That would be 3 insert commands.


Let's say your QID is 42. Do something like this for your first query:

DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 1, 'Yes')"

And the other two queries would be simialar

DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 0, 'No')"

DoCmd.RunSQL "INSERT INTO ResponseListTable (QID, ResponseListValue, ResponseListText) VALUES (42, 99, 'Unknown/Missing')"

I'm not sure whether you need a loop because I haven't spent enough time trying to figure out whether you need to bulk-insert or just do one a time.

Yes, definitely! Thank you for the response.
This is one half of what I would need. Im looking to bulk insert these values based on the "questionType" field. There could be any number of questions (QID) that require this insert.

Like: If questionType= "YesNo" then INSERT these values

 

[jal]

Ok, then, for a bulk insert here too you could use three queries (or one long messy query), and the first of these is something like this (not sure I got the syntax right).

INSERT INTO
ResponseListTable
SELECT QID, 'Yes' as ResponseListText , 0 as ResponseListValue
FROM QuestionTable
WHERE QuestionTable.QuestionType LIKE 'YesNo'

I can't recall if you named it "QuestionTable" nor whether it had a QuestionType field. But that's the general idea, I would think.

Or possibly you have to name the fields:

INSERT INTO
ResponseListTable (QID, ResponseListText, ResponseListValue)
SELECT QID, 'Yes' as ResponseListText , 0 as ResponseListValue
FROM QuestionTable
WHERE QuestionTable.QuestionType LIKE 'YesNo'

 

[SaviorSix]

Beautious! Thanks! repped