Enter Multiple Classes to a Record

[curiousdesi]

Good Morning!

I have a new scenario today! I have searched database design to try to figure this out and have an effective database as well as adding multiple records to one table that is linked to 1 record in the other table. Here's where I need help:

I have a master student list with their information that has the fields

Student ID
LastName
FirstName
Address
City
Zip
Community

I have another table that has the fields

ClassName
Date
Community
Instructor

What I would like to do is be able to create a form that can add multiple classes for each student. For example, I have Student 1. Student 1 has attended class A, B, C, and D. I would like the form to have the student's name and ID with a way to add multiple classes linked to that student. Is there a way to do this? I've looked into it and seem to find answers in which I can't see the db and don't understand code very well...

Any suggestions are more than welcome and I appreciate your time very much.

Thank you!!!

 

[curiousdesi]

Oh! and I completely forgot to mention that each class can have different dates so Student 1 has taken class A, B, C, and D. However, Student 1 has taken class A on the dates of January 1st, January 12th, and January 20th. Is this even possible?? I'm starting to get way too confused.

 

[Mihail]

Seems to be a classical MANY to MANY relationship.
First, you should create a new table:
tblClasses
ID_Class (Autonumber - PK)
ClassName
Instructor

Then
Another "bridge" table:
tblStudentsClasses
ID_StudentClass (AutoNumber - PK)
ID_Student (Number - FK)
ID_Class (Number - FK)
StartDate - Date/Time

 

[jdraw]

see this video for the concept of Many to Many

 

[curiousdesi]

Thank you guys for showing me it's many to many! My next question is thus:

Now that I have created the relationships, How can I create a form that will allow me to add a student along with all of the classes that are already in the courses table? I tried using a combo box to select from but it adds it as a new class with a unique class ID...

I seem to have an issue with making this user friendly. My ideal scenario would be to have a split form with the students' information and a subform with the Course information where they choose the correct course the student has taken by selecting the combo box. Example:

Student 1 has taken a citizenship class on 10/30/13, 11/6/13/ and 1/12/14.
I search for that student's record and find the subform. in the subform I choose Citizenship for the course name and the date they took it. Once the subform is finished entering, I want to be able to move on to the next record. Does that make sense?

 

[curiousdesi]

Is this even possible to do in a form?

 

[Mihail]

A form (single, with the first MANY) with a subform (continuous or datasheet, with the bridge table)

 

[curiousdesi]

A form (single, with the first MANY) with a subform (continous or datasheet, with the second MANY)

I'm sorry but I'm a bit of an access noob and I didn't understand this at all... What do you mean?

 

[Mihail]

Sorry, but I've make a mistake.
So:
Create a form based on table StudentsClasses: Should be a continuous form (Multiple Items button).
This will be your subform.

Be sure that Control Wizard button is activated.
Now, create the main form based on table Students (create this also as continuous form).
Enlarge the Detail section and insert a subform (Subform/Subreport button). Access will warn you that your form will be transformed in a Single form.
Say OK then, by following the wizard instructions, select the previous created form as subform to be inserted.

Now you have a form with a subform.
Do some tests in order to be sure that all is OK and, also, to understand how this works.

Finally you should hide the unnecessary ID's in both forms (main and sub)

Here is a DB (download the last one that is not split):
Beginner Advice for a Family Database - Access World Forums
from where you can see and, I hope, understand what I mean by studying the main forms and the subforms.

 

[curiousdesi]

I think I have something that works I'm sure that there must be a more efficient way but at least I have it working! Maybe I'll ask you guys more later you've been extremely helpful!!