I am creating a student assessment database. I have three tables thus far (as there is only one assessment at this point). One that stores student and teacher information and one that store the scores related to the first assessment. There are two lookup fields in the assessment table. One called ID which is the primary key and looks up ID from the student table and one called teacher which looks up last and first name of teacher in the teacher table.
Here is what I want. I want a form for easily entering student scores in the assessment table. I want the form to have sort of a lookup field where you can start typing the student's last name (obtained from student information I suppose) and then select it. After you select it, the ID field autopopulates (which is required by referential integrity in the assessment table) and then you can start typing in the scores. The reason for this is because your average person does not want to enter information by typing in a student # because they do not have that readily available. However, the name field is necessary beacuse it is the primary key.
Hope I was clear. Any help will be greatly appreciated.