I'm having problems setting up this relationship, please help

[Technyc2003]

I'm working on a database to keep track of courses our employees take at the job. I'm not sure how to setup my relationships based on the tables listed below. The database is going to look similiar to those of a purchase order type. There will be basic info regarding the student and then there will be a subform which will allow the data entry person to choose courses that employees are taking and apply it to their record. If you can help with this, it will be greatly appreciated. (Please note that the Courses table already has a list of all the courses in it and their Course ID value). I want the subform to look like this:

CourseID (Combo Box) once chosen it will fill in the information of the Course title, NoCredits, Instructor, Section, Completed. Afterwards I would like to save all the chosen data into a history table that shows all the courses that each student has taken. Thank you.

tblStudent
==========
StudentID (PK)
LastName
FirstName
HomeAddress
Etc..

tblCourses
==========
CourseID (PK)
Titles
Description
NoCredits
Instructor
Section
CourseCompleted (Y/N)

tblAgency
=========
ProgramID (PK)
AgencyName
AgencyType

 

[Harlequin007]

Techny

You need to create a foreign key in the tblCourses object. called StudentID. That way you can link your students to as many courses as you like.



----------------------------
Cogito Ergo Sum

http://www.discworks.co.uk

----------------------------

 

[KenReay]

Hi

Hmm..

where does tblAgency fit in?

I think you need to split tblCourses

tblCourses
==========
CourseID (PK)
Titles
Description
NoCredits
Instructor
Section

tblStudentCourses
==========
CourseID )PK
StudentId )
CourseCompleted (Y/N)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Technyc2003]

Thanks for responding. I guess I could've entered the agency names (there's about 12 of them) in a value list of a combo box but I wanted to keep them in their own table in case I needed to add more in the future. I did create a 1 to many between the course id of the tblStudent and tblCourses but I didn't know what to do with the tblAgency and that's when I posted this plea for help. I tried following the Nortwind database but that was just too complexed.

 

[KenReay]

Hi

On the information given it is not possible to comment on where the table tblAgency fits it, that is why I asked the question!

Do Employees belong to Agencies? or (as I suspect) are course run by Agencies?

Did you take onboard and agree/disagree with the other comments?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK