Simple(?) Database Design Question

[mkltmsck]

I'm very new to database design. I've built a database that consists of two tables, "questions" and "results". "questions" has an column "qns_id" which is an AutoNumber primary key, "Question" (text), 25 valid answer selections (all text). "results" has "qns_id" which is a Number foreign key, "Question" (text; same as on the "qns" table), "Answer" (the selected answer). Both tables also have other fields that are irrelevant to my problem.

I'm not using any forms in Access; the interface for this thing is being driven through excel using macros to query the database.

I've setup the primary/foreign key relationship properly for a one-to-many relationship from "qns" to "results". A one-to-many relationship does exist in this direction, so that's correct.

From here, I've scoured the internet for a few hours trying to find a way to get the foreign key on the "results" table to auto-populate according to the primary key associated with the question that uploads into the table. All indications from the internet are that I need to actually use a form to do this, but doing so seems like overkill. Admittedly, I don't know what forms are in Access, but if they're anything like forms in most other applications, (like infopath, for example), this really seems like a very heavy solution.

Here are my questions:
1. Is it possible to set up the table relationship such that Access can be smart enough to figure out the foreign key value and populate it on the "results" table without the use of forms (since I'm not running the UI out of Access)?
2. Am I misinterpreting what an Access form is, and if so, can you point me to a good tutorial on forms?
3. Regarding the layout of the two tables in question, am I sharing too much information across them, and should I perhaps yank the question column from the results table and replace it with just the foreign key?

I appreciate your help!
Mike

[ranman256]

I dont think so, you WANT the form to grab the master key and allow users to enter answers, thus creating the foreign key so the records dont become orphans. (its passive and the user needs to know nothing about keys)
Question on the master form, answers on the sub-form.
That is the beauty of Access forms.

[mkltmsck]

Hi ranman,

I'm not following your response. I don't want to use any forms whatsoever anywhere in this system- unless I'm forced to do so. Without forms, is it possible to update the foreign key directly from the primary key, or do I need to either build forms (and perhaps subforms), or modify my update queries in my excel macros, or... ?

[ranman256]

If you DONT use the forms (the easiest code free way), then you have a lot of work ahead. Macros, vba code to do this without forms.
Good luck.

[mkltmsck]

I appreciate your help, ranman.

This seems like a huge shortcoming of access, but like I said, I'm very new to database design. I realize this is an access forum, but is it pretty much the case across db technologies that foreign keys don't auto-update to match the primary keys, and thus such updates need to be done programmatically?

Having tried to do this with macros, I've become 100% certain that my database design is flawed. But I'm a combination of too new and too under the gun to sort that out now, and the mid-term prospects for this system *suggest* that it will be obviated by bigger and better, so fast & cheap wins out.

At the end of the day, I didn't want to use Access forms because they're not inherent to my system. The forms are all in excel, and all user interaction is done through excel. Therefore, I had to perform these updates via excel. I suspect I pretty much did the same thing as I would have done through access forms, and with very little code change in my excel macros, but I would have preferred to keep the database-specific operations... IN the database.