Make field auto populate based on value of field in another table

[paulcraigdainty]

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

 

[tehNellie]

Don't update your question field, add a new question record instead.

ie the record

id   Question                                       Answer
------------------------------------------------
1     "Robbie Williams is a talentless git."       "True"

Is static. When you want to create a new question you create a new record:

id   Question                                       Answer
------------------------------------------------
1     "Robbie Williams is a talentless git."       "True"
2     "Is Ronaldo a diving little cheat?"          "Yes"

Your scorecard table now relates back to the question id:

id  QuestionID  Score
----------------------
1      1          124 (or whatever you use)
2      2          156

To find out what the question scored use a simple query:

SELECT question, score
FROM tblquestion
INNER JOIN tblScore ON
tblquestion.id = tblscore.questionid

Which in this example will output:

Question                                   score
--------------------------------------------
"Robbie Williams is a talentless git."   124
"Is Ronaldo a diving little cheat?"      156

If you dont want to reuse questions you might need a "used" or "dateused" column in your question table, or you could use a variation on the query to pull questions that have never been scored and so on.