Table column for every row in another table???

[willbo987]

Hi, I dont even know if this is the right place to put this but will give it a go and try and explain my problem.

I have 2 tables:

Trainers
-> Name
-> Date of Birth
-> Address
etc etc.

Courses
-> Name of Course
-> Course approval expiry date
-> Type
etc. etc.


I now need to make a table whereby I can have the list of trainers and tick the courses that they are allowed to teach in each column.
I can forsee the columns are being:
-> Trainer
-> Course 1, course 2, course 3, course 4 etc etc.

The problem is that courses can be added/changed/deleted.

Any help would be much appreciated.

Will

 

[Mile-O]

You need a third table.

tblTrainers
TrainerID
Forename
Surname
DOB
Etc.


tblCourses
CourseID
CourseName
Duration
etc

tblTrainersToCourses
CourseID
TrainerID
StartDate
End Date

 

[willbo987]

Hi Mile-O.

Thanks for your help.

Although, I dont understand. Where does the StartDate and EndDate come into play.

Ideally I need the course names as column headings?

Thanks

Will

 

[Mile-O]

Although, I dont understand. Where does the StartDate and EndDate come into play.

That's just me thinking longer term. So while you assign a trainer and course, creating a many-to-many relationship, I would personally store dates as to when they were allowed to teach on those courses and when that stopped, if it has.

Ideally I need the course names as column headings?

No, don't do that. Don't ever consider it.

Each record in your Course table will have a single course. Each record in your Trainers table will have a single trainer. This extra table - search here on Junction tables for more information - is there to faciliate a many-to-many-relationship, because a trainer can teach multiple course and a course can be taught by multiple trainers.

Then, when your join the three tables in a query (TrainerID to TrainerID, CourseID to CourseID), you can pick and choose fields across the Course and Trainer tables.

 

[Galaxiom]

Ideally I need the course names as column headings?

This would NEVER be a normal table structure.

If you need to DISPLAY it like this then use a CrossTab query.

 

[willbo987]

Hi guys,

Thanks for your help.
I now understand... i think. So as you know I would preferably like the user to have checkboxes that they tick to say whether a trainer can do a particular course.

My question is, what table is this data stored in. I understand that I can query either a trainer to get the courses, or the courses to get the trainers, but a bit lost.

I have attached the query and relationships.

 

[willbo987]

Ok think I have come up with an idea but no idea how to implement.

If i have a check box on a form.

-> If the box is ticked, it runs an APPEND query adding the Instructor Name and Course Name into the JUNCTION table.

-> When the box is unticked, it runs a DELETE query deleting the instructor name and course name row

Could that work!!

 

[Mile-O]

Nobody said anything about checkboxes.

The data gets stored in the relevant tables, as long as the set-up is okay. Your structure and relationship is now good, although I would enforce referential integrity. You'll bind a query to a form and a query to a subform, and you can select away. Plenty of examples in this direction on the site. I put one in the Forms forum last week for, I think, a user called Hammotime.

 

[Mile-O]

Indeed it was this one: http://www.access-programmers.co.uk/forums/showthread.php?t=260636

Same sort of idea, although this was payments.

 

[Mile-O]

This may help, too.

 

[willbo987]

Top Man!!!