Need advice with relational database in Access 2007

[ClareLou80]

I work at a University and I am trying to put together a database that will store details about our students, the classes we provide and the instructors that teach the classes, but I need advice on how to link the tables so I can then run reports that will show a list of who is attending each class (students can attend 0-many classes), details of what classes are available and who teaches which classes (one instructor could teach more than one subject). The tables I have so far are as follows:

1 - Student Table
Student ID (Primary Key)
First name
Last name
Email Address
PE Classes enrolled on

2 - PE class Table
Class ID (Primary Key)
Class Name
Student ID
Instructor ID
Number of classes per term
Location

3 - Instructor Table
Instructor ID (Primary Key)
First Name
Last Name
Email address
Tel Number
Classes taught

I’ve searched the net for support (but I feel like a hamster in a wheel), I’ve tried online tutorials (but need someone real to ask questions to) and speaking to people who know about this stuff (but their time was short or they are in another time zone), but i'm still not getting it and I really want to learn. I have looked on this forum and if I have missed the answer, I apologize, I am new to all this.

Please suggest the best way to link my tables and get the information I need. Or if I am making a complete mess, please tell me and point me in the right direction.

Thanks for taking the time to read my post.

Clare

 

[ash1123]

dear,

Your design is simple fine, just join the primary key to the foreign key. make form.
make the foreign key as combo box which has a source from a query based from related tables. I think you will be able to do.
thanks

 

[ClareLou80]

Thanks for such a speedy reply ash1123!

I have now joined the tables up, PK to FK as you suggested. But you also say "make form". I know how to make a form, but can you please be more specific? What should be on the form?

Please talk to me like i know nothing, as that is how i feel at the moment!

 

[SteveH2508]

Your design is NOT fine. You have a many-to-many relationship between Student and PE class therefore you need a linking table between them.

1 - Student Table
Student ID (Primary Key)
First name
Last name
Email Address

2 - PE class Table
Class ID (Primary Key)
Class Name
Instructor ID
Number of classes per term
Location


NB – the number of classes per term should not be stored – you should store the Start date and then derive that info if you need it.

3 - Instructor Table
Instructor ID (Primary Key)
First Name
Last Name
Email address
Tel Number


4. – Enrolment Table
Enrolment ID (Primary Key – Autonumber)
Student ID
Class ID


NB If you want to prevent a Student enrolling in a class more than once you can either:
Create a composite Primary Key across Student ID and Class ID or create a unique index across those two fields.


Another tip - do not have spaces in your field names - it makes queries and expressions that bit more tricky.

 

[ClareLou80]

Thank you so much for the advice Steve! I am going to go away and put it into action and then see what happens next!!!

 

[ClareLou80]

Ok, so I have ammended the tables as you suggested (spaces all gone), added in the enrollment table and also added in a few forms, but still can't get my head around how i enroll a student on a class?

If i go to the enrollment table, how would i know which student is which and which course is which as the table only displays the ID's?

I have attached my database now. Can you please offer so advice on what i need to do next?

I've spent most of the afternoon trying to answer my own questions, searching the net and forum, but again, i feel a little lost!!!

 

[SteveH2508]

Your tables were still not quite right - I have removed the StudentID from tblPEClasses.

Look at FrmStudentNEW and its associated sub-form.

I have added a combo box to select a particular course.

frmStudentNEW has a search combo to find a particular student record.

Hope that gets you started.

 

[ClareLou80]

WOW! thanks Steve, you have quite possibly saved me a mental breakdown!

Can you suggest somewhere i could learn about combo-boxes? What you have done works perfect and i can see how i could use something similar in other scenarios.

Thanks again!

 

[SteveH2508]

Here is a start

 

[ClareLou80]

Once again Steve, THANKS!!! Your help has been invaluable and much appreicated.