Need help with table relationships! (1 Viewer)

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
I am attempting to create relationships between my tables and am struggling mightily :banghead:

There will be many students (student demographic tbl), some of which will have completed many programs (program tbl), all students will have completed many assessments (assessment tbl), some students will have many clinicals (clinical tbl), all students will have only one admission (admission tbl), and the tblP_Reports is a stand alone and needs no relationship.


Image of tables attached.


Thank you so much for your time!!
Doug
 

Attachments

  • Bonesdb.png
    Bonesdb.png
    40.2 KB · Views: 182

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
21,358
Hi Doug. Welcome to the forum. I usually don't create 1-to-1 relationship tables, but if the StudentID field in the Demographics table is an Autonumber field, I hope the StudentID field in the Admissions table is not an Autonumber field too. It should just be a Number (Long) field.
 
Last edited:

Bullschmidt

Freelance DB Developer
Local time
Today, 16:51
Joined
May 9, 2019
Messages
40
It looks to me like you have things set up nicely with StudentID as the primary field in tblStudent_Demographics and also as a secondary field in most of the other tables so that in queries there can be JOINs as needed.

As with theDBguy I also notice that with an implied 1-to-1 relationship it seems like you could combine tblStudent_Demographics and tblStudent_Admission into one table possibly just called tblStudent.
 

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
Hello theDBguy. The StudentID field in the Admissions table is just a number field. I appreciate your 2 cents :)
 

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
Bullschmidt........I like that username :). Thank you for your reply!
 

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
Bullschmidt and theDBguy, are you both in agreeance that I can just relate the studentID in all tables then? Even if some of the tables have many entries per student? If that is the case, when I create my forms, should I just create subforms for those forms that need multiple entries? Thanks again to both of you for your time!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
21,358
Bullschmidt and theDBguy, are you both in agreeance that I can just relate the studentID in all tables then? Even if some of the tables have many entries per student? If that is the case, when I create my forms, should I just create subforms for those forms that need multiple entries? Thanks again to both of you for your time!
Hi. Can you post a copy of your db with just the empty tables in it? If you do, I'll try to create the relationships between the tables and you can compare them to how you would have done it on your own.
 

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
Awesome! Here you go theDBguy. Thank you so much.
 

Attachments

  • Bones.accdb
    672 KB · Views: 170

theDBguy

I’m here to help
Staff member
Local time
Today, 14:51
Joined
Oct 29, 2018
Messages
21,358
Awesome! Here you go theDBguy. Thank you so much.
Hi. Thanks. This is by no means complete, but I hope it would be a good start for you.




Some recommendations for you.
1. Avoid using spaces in your fields' names
2. I changed some primary keys to an Autonumber field. If you can, I recommend you change all of them. It's difficult to use a Text field as a primary key
3. I added a Programs table since several students will probably be in the same program. If so, this is actually a many-to-many relationship.
4. In the same token, you may need to add an Assessment table too. However, I started out by changing the PK to an Autonumber field and added a Text field for the assessment type, which probably belongs in its own table.


Hope it helps...
 

Attachments

  • relationship.PNG
    relationship.PNG
    54.7 KB · Views: 326
  • Bones.zip
    29.7 KB · Views: 185

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:51
Joined
Sep 12, 2006
Messages
15,614
Just for info, the big thing is the junction table.

You can only model a 1-many relationship (1-1 is a special sort of 1-many)

if you have a many to many relationship then you have to devolve it into two 1-many relationships

So if you have tables for Students and Courses, you necessarily have students doing multiple courses, and courses with multiple students

So you need a third table called student-courses

and your relationships become
Student - 1 to many - StudentCourses - many to 1 Courses

This is similar to the theDBGuys junction table tblStudent-Programs - which joins the Student-Demographics table to the Programs table via the junction table.

A junction table often doesn't have many other fields, but in this case does. The student-programme record in the junction table should be a unique entry (the same student should only do the program once) - so it is convenmient to store info about this allocation in the junction table. If you need courses to be repeated ever, then the model might need to change to reflect that.

(I presume the scrolled off fields at the top of the Student Demographics include the student names and address and personal info, etc, as well as a lot more beside.)

--------------------
when you decide to store a new bit of data, you either already have a logical table in which it should go, or you need a new table. In that case you need to decide how the new table needs to relate to other tables in your database, whicle maintaining 1 to many relationships.

eg - if you wanted to record staff members responsible for the various student assessments.
 

bookerd

New member
Local time
Today, 14:51
Joined
May 28, 2019
Messages
7
Thank you Dave. This is good info! Much appreciated.
 

Users who are viewing this thread

Top Bottom