One-to-Many Relationships and how it works with Queries

[lacey]

Hello.
I have set up a one-to-many relationship between my tables with a junction table. I am new to junction tables but I believe I set it up right. I have created a query from the tables, but the query isn't returning any records which leads me to believe I set the relationships up wrong after all!

Here is my relationships document:
View attachment 37203

Any ideas?

 

[jzwp22]

Your session tables and their corresponding junction tables are not right. Your session table should hold just information about the session (i.e session 1, session 2 as RECORDS not fields); like data should be in 1 table. Also it is recommended not to have spaces or special characters in your table or field names

tblStudent
-pkStudentID primary key, autonumber
-txtFirstName
-txtLastName
other fields

tblSessions (session 1 , session 2 etc as RECORDS)
-pkSessionID primary key, autonumber
-txtSessionName

tblStudentSessions (assigns the sessions for each student)
-pkStudentSessionID primary key, autonumber
-fkStudentID foreign key to tblStudent
-fkSessionID foreign key to tblSessions

Now if a particular session has multiple activities (LEGO Jr., Kids Hip Hop), that is another one-to-many relationship, so the activities should be RECORDS in a related table not fields. If the activities related to a particular session are held in a specific sequence, you can add a number field to denote the order of the activities. I don't know if this would be equivalent to your period # since all period numbers were the same in session tables in your post. You might have to explain that part further.

Something like this (I'm guessing)

tblSessionPeriods
-pkSessionPeriodID primary key, autonumber
-fkSessionID foreign key to tblSessions
-txtActivity
-longSequence

 

[lacey]

You are right, there are 4 periods per session. I believe I will need to create another table for periods, then...

Does this look right so far?

View attachment 37239

 

[lacey]

Another thing... if I am to create another one-to-many relationship for each Class, how would I go about that? There are multiple classes (what you refferred to as activities) per session, per period. However, some classes are offerred in more than one session. What would be the best way to build that?

 

[jzwp22]

Does this look right so far?

Nope



The periods should be records in a table not sequentially numbered fields in the table.

Do you call the periods by the same name such as period 1 for session 1 and period 1 for session 2 or do you use unique names for each period?

Just saw your new post:

There are multiple classes (what you refferred to as activities) per session, per period. However, some classes are offerred in more than one session. What would be the best way to build that?

So you have session and a session can have many periods and a period can have many classes? A class may occur in multiple periods which in turn can be related to many sessions--is that what you are saying?

 

[lacey]

We use the same. Session 1 has Period 1, 2, and 3, and Session 2 & 3 have the same.

 

[jzwp22]

OK, then we can have a table that holds the periods

tblPeriods
-pkPeriodID primary key, autonumber
-txtPeriodName


Now relate the applicable periods to the sessions

tblSessionPeriods
-pkSessPeriodID primary key, autonumber
-fkPeriodID foreign key to tblPeriods

Now since a class can take place in multiple periods, we should have a table to hold all possible classes

tblClasses
-pkClassID primary key, autonumber
-txtClassName


Is there only 1 class associated with a Session/Period combination or multiple classes? In other words during 1 period of a session are there multiple class or only 1?

 

[lacey]

OK, then we can have a table that holds the periods

tblPeriods
-pkPeriodID primary key, autonumber
-txtPeriodName


Now relate the applicable periods to the sessions

tblSessionPeriods
-pkSessPeriodID primary key, autonumber
-fkPeriodID foreign key to tblPeriods

Now since a class can take place in multiple periods, we should have a table to hold all possible classes

tblClasses
-pkClassID primary key, autonumber
-txtClassName


Is there only 1 class associated with a Session/Period combination or multiple classes? In other words during 1 period of a session are there multiple class or only 1?

OK, I created the above tables...

View attachment 37240

To avoid confusion, here is how the whole program is set-up:

Session1:
Period 1:
-Class 1
-Class 2
(etc.)
Period 2:
-Class 1
-(etc.)
Period 3:
-Class 1
-(etc.)
Period 4:
-Class 1
-(etc.)
Session2:
Period 1:
-Class 1
-Class 2
(etc.)
Period 2:
-Class 1
-(etc.)
Period 3:
-Class 1
-(etc.)
Period 4:
-Class 1
-(etc.)
Session3:
Period 1:
-Class 1
-Class 2
(etc.)
Period 2:
-Class 1
-(etc.)
Period 3:
-Class 1
-(etc.)
Period 4:
-Class 1
-(etc.)

There are about 11 or 12 classes per period and 48-ish classes per session. I am trying to contact our program director to find out if one class may appear more than once per session.

 

[lacey]

I just found out--YES, one class may appear more than once per session. Just not per period.

 

[jzwp22]

Since the class would relate to the period there would be no problem if it occured in another period within the same session.

We need 1 final table to handle the classes within a period

tblSessionPeriodsClass
-pkSessPerClassID primary key, autonumber
-pkSessPeriodID foreign key to tblSessionPeriods
-fkClassID foreign key to tblClasses

 

[lacey]

Okey-dokey, here's what I have now.

View attachment 37275

Yeah?

 

[jzwp22]

Overall the tables and relationships look OK. It is generally recommended to not have spaces or special characters (#,&,/,|,$,\,% etc.) in your table or field names. You have the following: First Name, Last Name, Parent/Guardian Name, Class Name etc. that should be changed.

Relative to the birthday field you have. It probably would be best to store the actual date of birth. You can have Access calculate the age of the person based on the current date and the date of birth.

Technically speaking, the students and their parent/guardians are all people, so you might consider putting all people in 1 table and then have a field that distinguishes their role (student, parent/guardian). Additionally, several students may have many parents/guardians (mom, dad, grandparent etc.). With your current structure you would only be able to enter 1 person in the parent/guardian field.

 

[lacey]

Okay, got it. I will do those things next.

If I could ask you another piece of advice... what would be the best way to set this up in my form? I would like to have the student's info and class info separated onto two tabs for easier maneuvering. Should I create subforms with the class/session info?

 

[jzwp22]

In general, you would have a main form based on the table that makes up the one side of the one-to-many relationship and a subform based on a table that makes up the many side of the relationship. Since a student is related directly to a session via the studentsessions table you would have a main form based on the student and the subform based on studentsessions. On that subform, you would use a combo box whose row source is the sessions table. Now since there is not a direct relationship between the student and the classes, you would not be able to have a subform showing the classes on the student main form. You could have a button on the studentsession subform that when press could open up another form that shows the periods & class relative to that session. It will depend on how you want to present the data to the user. I'm not really sure what you are after...

 

[lacey]

So I should create another form for classes that will be opened when the button is pressed?

 

[lacey]

How would I set it up to display the classes only offered in whichever session was selected?

 

[lacey]

This is a completely different topic now, so I'm going to move my question over to the forms discussion board.

 

[jzwp22]

I would recommend that you start off with some simple forms at first to see how they work. Then you can start experimenting with command buttons (using the command button wizard will help)