Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-28-2019, 12:01 PM   #1
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Need help with table relationships!

I am attempting to create relationships between my tables and am struggling mightily

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
Attached Images
File Type: png Bonesdb.png (40.2 KB, 47 views)

bookerd is offline   Reply With Quote
Old 05-28-2019, 12:12 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,091
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Need help with table relationships!

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 05-28-2019 at 12:19 PM.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
bookerd (05-28-2019)
Old 05-28-2019, 12:14 PM   #3
Bullschmidt
Newly Registered User
 
Bullschmidt's Avatar
 
Join Date: May 2019
Location: USA
Posts: 22
Thanks: 0
Thanked 4 Times in 4 Posts
Bullschmidt is on a distinguished road
Re: Need help with table relationships!

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.

__________________
Freelance Web and Database Developer

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Bullschmidt is offline   Reply With Quote
The Following User Says Thank You to Bullschmidt For This Useful Post:
bookerd (05-28-2019)
Old 05-28-2019, 12:37 PM   #4
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

Hello theDBguy. The StudentID field in the Admissions table is just a number field. I appreciate your 2 cents
bookerd is offline   Reply With Quote
Old 05-28-2019, 12:39 PM   #5
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

Bullschmidt........I like that username . Thank you for your reply!
bookerd is offline   Reply With Quote
Old 05-28-2019, 12:43 PM   #6
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

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!
bookerd is offline   Reply With Quote
Old 05-28-2019, 12:47 PM   #7
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,091
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Need help with table relationships!

Quote:
Originally Posted by bookerd View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-28-2019, 12:56 PM   #8
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

Awesome! Here you go theDBguy. Thank you so much.
Attached Files
File Type: accdb Bones.accdb (672.0 KB, 19 views)
bookerd is offline   Reply With Quote
Old 05-28-2019, 01:20 PM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,091
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Need help with table relationships!

Quote:
Originally Posted by bookerd View Post
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...
Attached Images
File Type: png relationship.PNG (54.7 KB, 93 views)
Attached Files
File Type: zip Bones.zip (29.7 KB, 22 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-28-2019, 01:33 PM   #10
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

This is great, and much appreciated!
bookerd is offline   Reply With Quote
Old 05-28-2019, 01:37 PM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,091
Thanks: 36
Thanked 735 Times in 718 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Need help with table relationships!

Quote:
Originally Posted by bookerd View Post
This is great, and much appreciated!
Hi. You're welcome. Let us know how you get along. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 05-28-2019, 02:31 PM   #12
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,768
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Need help with table relationships!

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 05-28-2019, 02:59 PM   #13
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 733
Thanks: 3
Thanked 150 Times in 144 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Need help with table relationships!

cross posted
http://www.accessforums.net/showthread.php?t=77027
Micron is offline   Reply With Quote
Old 05-28-2019, 03:42 PM   #14
bookerd
Newly Registered User
 
Join Date: May 2019
Posts: 7
Thanks: 2
Thanked 0 Times in 0 Posts
bookerd is on a distinguished road
Re: Need help with table relationships!

Thank you Dave. This is good info! Much appreciated.

bookerd is offline   Reply With Quote
Reply

Tags
database beginner , database design , relationships , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Relationships - linking reference table field to 2 main table fields HGCanada Tables 2 01-09-2017 11:51 AM
Table Relationships alex44 Theory and practice of database design 5 09-20-2013 08:51 AM
Relationships between table - 3 fields in 1 table related to another same table laiching Forms 2 01-16-2012 06:20 PM
How to deal with table relationships where table is deleted and Inserted darbid SQL Server 5 09-01-2010 10:15 PM
Need help with relationships in table Rare87GT Tables 25 11-27-2004 04:24 PM




All times are GMT -8. The time now is 12:39 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World