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