Fields not linking between tables

[Guest]

I have created a database of training courses and trainees.

Table 1 = Course Data (course name, date, venue, trainer, invoice no. etc.)
Table 2 = Trainee Data (trainee ID, first name, last name)

I created a Course Data Form using Table 1 and included a sub form for
trainees using Table 2.

I had originally linked these tables together using the invoice no., which
worked great until I was informed that more than one course name can appear
on one invoice and therefore create duplicate information in Table 2.

I have created another field in both tables named Course Index ID.
In Table 1 I have made this an autonumber field and the Primary Key.
In Table 2 I have made this a number field, leaving Trainee ID as the
Primary Key.
I have created a relationship between the Course Index ID field in both
tables, which is how it worked for the invoice no. field.

However, the Course Index ID field in Table 2 remains empty, what am I
missing?

 

[Jeff Boyce]

Your design appears to be lacking a table that shows which Trainee signed up
for which Course (I am assuming that one Trainee could register for more
than one Course, and one Course could have more than one Trainee
registered.)

Your data structure might look like:

tblCourse
CourseID
CourseTitle
CourseTrainerID
... other facts specific to the course only

tblTrainee
TraineeID
FirstName
LastName
DOB
... other facts specific to the Trainee only

tblTrainer
TrainerID
FirstName
LastName
... other facts specific to the Trainer

NOTE: if Trainee and Trainer look a lot alike, it's because they both are
Person. Would you ever be faced with the possibility that a Trainer of one
Course might sign up as a Trainee in another? Or a Trainee at one point in
time could "grow up" to be a Trainer?

trelRegistration
RegistrationID
CourseID
TraineeID
RegistrationDate
... other facts specific to the registration only

And be aware, if you could have the same Course offered more than once,
presented by different Trainers each time, you'll need even more tables!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I do have other tables, but I was trying to keep the question simple.

"Course Data" table and "Trainees" table are the two I want linking together
with "Index No".

Why does the "Invoice No" which is typed into "Course Data" table appear in
the "Trainees" table, but not the "Index No"? Is it something to do with the
Autonumber? As the "Trainees" table is just being populated with "0" in the
"Index No" field at the moment.

 

[Jeff Boyce]

I have no idea why... I can't see your database.

Is there a chance your tables have one/more "Lookup" data type fields? This
can cause some confusion because they store one thing but display another.

How did you relate the subform to the mainform? These seem more like .forms
newsgroup questions than .tabledbdesign questions.

Regards

Jeff Boyce
Microsoft Office/Access MVP