Relationships

[teetertotter57]

In my database, I have 2 tables, well there are more but the questions pertains to 2 specific tables. Table1 ScheduledClasses, Table2 Participants. I want to put several participants into the scheduled class. Each participant takes multiple classes, which I want to show in the Participant table. I'm not sure whether to join them by ParticipantID or ClassID. I'm thinking one would remove the participant each time I put him in a different class and the other would allow him in all three classes. By which key field should I join them?

Thank You,
Regina

 

[plog]

Many particpants can take many classes = many to many relationship. The proper way to handle this is with a junction table (http://en.wikipedia.org/wiki/Junction_table). You set up a 3rd table to keep the connections between these two tables. It would look like this:

ClassParticipants
ClassID, ParticipantID

Then when you need to see what particpant is taking a class (or who is in a class) you link these 3 tables:

Participants.ParticipantID -> ClassParticipants.ParticipantID
ScheduledClasses.ClassID -> ClassParticipants.ClassID

 

[teetertotter57]

I should have posted this under a different forum (Tables), but I'm new at this.

I think I've figured it out, but if someone could take a look, please.

Tbl Instructor
Instructor (PK)
Name
Add...etc

Tbl Locations
LocationID (PK)
Address...etc

Tbl ScheduledClasses
ClassID (PK)
InstructorID (FK)
LocationID (FK)
ClassDate

Thank You,
Regina

 

[teetertotter57]

I'm really new at this. I just finished a basic class. What do you mean by junction table?

Thank You,
Regina

 

[plog]

I provided a link so that you could find all about them. Then I explained the practical application of one in your system.

 

[teetertotter57]

Plog,

I don't see a link. There is just an ad. Where do I find your link?

Thank You,
Regina

 

[jdraw]

PMFJI,

http://en.wikipedia.org/wiki/Junction_table

For a video on using a junction table

 

[teetertotter57]

jdraw,

Ok, so the ScheduledClasses table would be the junction table. Thank Ya'll, so much. I'm going to play, now.

Regina

 

[plog]

ScheduledClasses is a junction table, but not for the issue you initially posted about. ScheduledClasses defines a many-to-many relationship between instructors and locations (locations can have many instructors and instructors can be at many locations).

For your many to many relationship between Participants and Classes you need another table...as I described in my initial post.

 

[teetertotter57]

Plog,

Ok, I see. The instructor table is actually the participant. We train law enforcement in less lethal. Each agency sends one instructor to a class. The instructor may attend 3 classes, then is recertified in each annually. I was looking at another one of your posts, which was really helpful, too.

Thank You. If I get stuck I'll come running back.
Regina

 

[teetertotter57]

Ok, I created my junction table. I think I'm going to love this, by the way. Now it looks like this:

tblInstructor
InstructorID (PK)

tblScheduledClasses
ClassID (PK)

junctionClassParticipants
InstructorID
ClassID

So, now, do I add fields to the junction table, for example ClassDate, ClassType, ClassStatus, then create a form based on the junction table? I'll need to enter each instructor in their respective classes. I was reading about multi-valued fields and I don't think I want to go that route...headache, headache.

Thank You,
Regina

 

[jdraw]

Perhaps you should step back and work through this tutorial.

 

[teetertotter57]

jdraw,

The tutorial looks like it's exactly what I need. I just printed it. I actually learn best this way.

Thank You,
Regina

 

[teetertotter57]

I started a brand new database just to play with designing a junction table. This is what I have.


ScheduledClasses Table
ClassID PK
ClassDate
Host
Location
Address
City
State
Zip


Students Table
StudentID PK
Agency
Title
First
Last
Address
Phone


JunctStudentsClassesTable
StudentID PK
ClassID PK
ClassDate
Host


My Forms:
ScheduledClasses Form
ClassID PK
ClassDate
Host
Location
Address
City
State
Zip
ContactInformation


StudentsForm
StudentId PK
Agency
Name
etc...


ClassEnrollment Form (Based on the JunctStudentClasses table) This is my main form, to which I'll ad the subform Students
ClassID
ClassDate
Host


I entered class information in the ScheduledClasses table and a student in the Student table. When I enter information in the junction table I get a message that I can't do that because a related record needs to be in the ScheduledClasses table. Well, there is one. I created this database because I was getting the same message in the other one, so I thought it better to quit messing with it and play in a new one until I get this down.


I tried this in my other database, with a subform included on the parent form and still got that message.


I really thought I understood this. What am I doing wrong?

Thank You,
Regina:banghead:

 

[jdraw]

See if this free data model helps.
If not then Post a zip copy of your database and I'll take a look.

 

[teetertotter57]

Jdraw,

I'm not seeing a model, just an ad for the cloud.

Thank You,
Regina

 

[jdraw]

Sorry about that. I was typing and got a Skype before I added the link.

I have added it now.
Good luck.

 

[plog]

Here's the issues I see

Tables:
You have redundant fields. Host and ClassDate fields should be in either ScheduledClasses or JunctStudentsClasses, not both.

Forms:
ScheduledClasses has a ContactInformation field, but no related field in ScheduledClasses table. Where is it?

If ClassEnrollment is to be part of a form with a subform, it should be the subform. Either have a Classes form with a ClassEnrollment subform where you add students, or have a Students form with a ClassEnrollment subform where you add them to classes. ClassEnrollment can be it's own form, but it would not have a subform.

 

[teetertotter57]

Jdraw,

Thank you, very much. That helped.

I don't know how I did it and I know I still don't quite get it, but I managed to create a main form for my students with a subform, based on the junction table. I changed the ClassId to a combo box and in the window selected the ScheduledClasses table. It seems to be working perfectly, however, I still don't know if this is correct. If it is, oh my gosh, I'm going to have fun with these.

Also, in the table model you sent me, I noticed 3 FK's in the Classes table. Are all these combined PK's in one junction table? I also saw this example in the Roster table. Can you combine more than 2 FK's in one junction table?

Thank You,
Regina

 

[teetertotter57]

Plog,

You are right and I've corrected that, but thank you for pointing it out. I just as easily could have missed it, especially in my frantic state of trying to work this out.

Thanks,
Regina