A simple, yet frustrating question from a newbie

[mefloquinedream]

Hey all,

Just found the forum -- love it. I'm hoping it'll help me out with a lot of my problems. I've extensive experienced coding for PDAs, and with other databases or VBA-based programs, but I've just run into a frustratingly simple problem with an Access database I'm building for medical school (I'm a second-year med student).

Here's the situation: I've two linked tables -- e.g., TblNeuroQuestions and TblNeuroAnswers -- in which the primary key in both is the Question # field. I've created a form to easily enter data enter the questions table (question #, question, and 5 answer choices.) What I want to do is have the question text automatically be copied into the corresponding Question field in the TblNeuroAnswers table, so that I don't have to do it manually (the structure of the TblNeuroAnswers is merely Question #, Question, Answer (A,B,C,D,E), and Explanation). In other words, I want the entry of the question data in the main form (the NeuroQuestions form, which enters data in the TblNeuroQuestions table) to automatically be entered in the corresponding Question field of the TblNeuroAnwsers table.

It seems like it should be an easy thing to do; run a simple update query, for example. But I'd like the form do dual entry, so to speak. Any suggestions?

 

[Banana]

Sounds like a normalization problem. I'd *strongly* advise that you research the normalization.

But a quick'n'dirty answer: Never, never, ever in any circumstances, should you store *same* data in two places. That's why we use keys; we store data in only one location, and use keys all over the place.

Therefore a properly normalized structure would be something like below. I am not completely sure if I understand your needs, but I assume that there's one answer out of five different possible for all questions. If that is true, then the below structure is correct. Otherwise, we'll need to modify it a bit.

tblQuestions

QuestionID (Primary key)
QuestionText

tblAnswers
AnswerID (Primary Key)
PatientID (I am assuming this is for a patient. Answer should be a junction table; this should be be the foreign key)
QuestionID (foreign key to Question table)
Answer

There is no need to copy question text around. If you need to see the text, use a query instead.

 

[mefloquinedream]

Thanks for the prompt reply. I appreciate it.

I had wanted to see the question with the appropriate answer and corresponding explanation when I generated the report from the TblNeuroAnswers table. But I guess I could have just done that with a query and a properly-normalized structure. I've just been so busy cramming data INTO the database that I haven't given much thought to its overarching normalization. That was probably a mistake.