Help with Code/Tables/Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Currently, in my database there are two main tables. One is for student info
(Name, address) and the other is for test visits Data(Instructor, Date, test
name, etc). So, what is happening now. New records are created for both
tables everytime a student signs in. I would like to only create a new
record in the student table if there is no other record for that student The
data table would still create a record for each specfic vist.

To acheive this I felt it would be best done in the form for the student
sign in, but I dont know where to start with the coding.

Any help would be great.

David
 
Hello,

Currently, in my database there are two main tables. One is for student info
(Name, address) and the other is for test visits Data(Instructor, Date, test
name, etc). So, what is happening now. New records are created for both
tables everytime a student signs in. I would like to only create a new
record in the student table if there is no other record for that student The
data table would still create a record for each specfic vist.

To acheive this I felt it would be best done in the form for the student
sign in, but I dont know where to start with the coding.

Any help would be great.

David
Click to expand...

If each student can be associated with multiple test visits, and each
test visit can be associated with multiple students, you have a Many
to Many Relationship - and this requires a *THIRD* table.

Your Students table should contain information about Students (and
nothing about visits). Each Student should be represented by only one
single record in Students. There must be a Primary Key field, a unique
ID (StudentID let's call it) - student names will NOT work, as they
may not be unique (I have three friends named Fred Brown).

Similarly, the Visits table should have info about the visit (as you
cite) - nothing else. It should also have a Primary Key field.

This third table, StudentVisits, would have fields to link to the
StudentID and VisitID fields, along with information about *this*
student and *this* visit.

For data entry you don't need any "formscoding" - just a simple Form
based on the Student table (or perhaps the Visits table), with a
Subform based on this third table.

John W. Vinson[MVP]
 
Back
Top