Link Child/Master Field

  • Thread starter Thread starter Rhys
  • Start date Start date
R

Rhys

The database I'm working on has many, many fields, but the
bare bones of the bit I'm struggling with is this:

Table 1: Schools
Fields:
School Name (Primary Key)

Table 2: Tutors
Fields:
School Name (linked to "School Name" in Above table)
Tutor Name (Primary Key)

Table 3: Students
Fields:
School Name
Tutor Name (Linked to above table)
Student Name

These three tables are nested, but I would like the third
table to automatically enter the school name once I have
done it in the "schools" table. I think Link Child/Master
Fields is the way to do it, but the instructions in MS
help really don't tell you very well how to do this.

Any help will be much appreciated

Rhys
 
Hi Rhys,
Are you trying to explain the problem with a form or a database?
If you enter a new record in a database for a new school, are you then
suggesting that you want to create a new record in the tutors file
with a blank tutor and then a new record in the Students table with a
blank Tutors and Student ?
I am not sure why you would want to do this it doesn't seem to make
sense so I guess I am misunderstanding ?
Also it may be better to define your table slightly differently with
the Tutors table having a Primary key that is a combination of
School/Tutor to ensure uniqueness - otherwise you may have a tutor
duplicated if the same name is in two schools. Likewise the Student
key would be School/Tutor/Student as the primary key.
The ideal way would be to have an autogenerated primary key (use
Autonumber) as the first (doesn't have to be) field in each table -
School_ID number (autonumber - Primary Key)
SchoolName (text)

Tutor_ID number (autonumber - Primary Key)
School_ID number (a foreign key to the School record)
TutorName

Student_ID number (autonumber - Primary Key)
Tutor_ID number (a foreign key to the Tutor table)
StudentName

Then all the table joins will work e.g.
Select StudentName from tblStudent,tblTutor,tblSchool
where
tblStudent.Tutor_ID = tblTutor.Tutor_ID
and
tblTutor.School_ID = tblSChool.School_ID

OR in correct SQL
Select StudentName from tblStudent
join tblTutor on tblStudent.Tutor_ID = tblTutor_ID
join tblSchool on tblTutor.School_ID = tblScool.School_ID

If you use this hierarchical Schools->Tutors->Students then your form
can be a Main form of School with a subform of Tutor which in turn has
a sub-sub form or Student and they'll all lionk together using the
Parent/child relationship in the form queries.
 
Back
Top