Microsoft Access Relationships: Relating Multiple Tables, Relational Data, One-to-Many, Many-to-Many

In this video you will learn about relationships in Microsoft Access. You will learn how to relate data from different tables together, and why it's important for good database design.
Simone from Bridgeport, Connecticut (a Platinum Member) asks: I'm a realtor. I'm in the process of setting up my first Access database. I've been using Excel for years to track my customers and the houses they've purchased. Once in a while I get a customer who has purchased multiple houses. Should I make a separate entry for each of them in my customer table? It seems like this would be an awful lot of duplicated data.
Silver Members and up get access to an Extended Cut of this video. Members will learn about Self Joins, Global Relationships, and Referential Integrity which is where you can prevent entry of a child record without a matching parent record, or delete a parent if it has children. We'll also talk about cascade updates and deletes and why I personally don't use them.
MEMBERS VIDEO:
• Relationships - Self J...
BECOME A MEMBER:
KZbin: / @599cd
or My Site: 599cd.com/THMe...
LEARN MORE:
599cd.com/Rela...
LINKS:
Subforms: 599cd.com/Subform
Open Form Specific Data: 599cd.com/Open...
Blank Template: 599cd.com/Blank
Contacts: 599cd.com/Cont...
Access Expert 1: 599cd.com/ACX1
Relationships Seminar: 599cd.com/Rela...
COURSES:
FREE Access Beginner Level 1: 599cd.com/Free1
$1 Access Level 2: 599cd.com/1Dollar
FREE TEMPLATE DOWNLOADS:
TechHelp Free Templates: 599cd.com/THFree
Blank Template: 599cd.com/Blank
Contact Management: 599cd.com/Cont...
Order Entry & Invoicing: 599cd.com/Invo...
More Access Templates: 599cd.com/Acce...
ADDITIONAL RESOURCES:
Donate to my Tip Jar: 599cd.com/TipJar
Get on my Mailing List: 599cd.com/YTML
Contact Me: 599cd.com/Contact
TechHelp: 599cd.com/Tech...
Consulting Help: 599cd.com/DevNet
Twitter: / learningaccess
en.wikipedia.o...
products.offic...
microsoft.com/...
KEYWORDS:
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, lrelationships, relational, one-to-many, one-to-one, many-to-many, relate data, self join, global relationships, edit relationships, referential integrity, parent, child, matching, cascade updates, cascade deletes
QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[@ButterflyEars60]

Thank you so much for these videos! I was an advanced user some years ago. I created databases for tons of employee data, applicants....everything related to employment in a school district of 6,000. It was a life saver! I am crazy about Access. I'm trying to create a few databases and realize I have forgotten some key things about all of this. It's great to watch your videos and get refreshed on some things. I intend to take at least one of your advanced classes to refresh myself on the really good stuff!

[@599CD]

Sounds great. Welcome back. :)

[@ancestrycounts4759]

Thank you, I've been watching, by different people, various beginner access videos. One person would do a table to table link and another person do a table to junction to table link. Since each person was only showing the one example, I couldn't understand the underlying difference between the two methods. Then I found this video! Got it, light bulb went on! Starting your beginner class now.

[@Addicted2Learning]

Clear explanation, well-paced!

[@599CD]

thanks

[@xlschool7334]

Thank you for amazing and clean explanation about table relationships. I have 2 tables a student table and a teachers table, every student has to have a teacher as an advisor and a supervisor, now what type of relationship these two table can have. I mean every student simultaneously has relationship with two different record with teachers table. Highly appreciated for response and instractions

[@599CD]

You need a 599cd.com/ManyToMany relationship

[@kurosakibankai19]

Very helpful and informative videos. P.S. love the Star Trek references and not just any Original XD

[@599CD]

LLAP. :)

[@xm_tech]

Thanks for sharing this video. It’s very helpful

[@599CD]

Welcome

[@DoWithAbdou]

BEST TUTOR EVER 👌💯

[@599CD]

Thanks

[@ishmaillibbie8238]

Thanks a lots for such a wonderful tutorial. I have never open access before but I just started watching your four (4) hours plus video and from what I'm seeing, you are really good in it. God bless you for such help and besides I really want to watch from start to finish. Please help me to access the videos from lesson 1 to the last lesson please.

[@599CD]

All of my lessons are here: 599cd.com/Access

[@dniezby]

Deana Troi? LOL. Love it. As you were number one.

[@599CD]

Make it so

[@philipbbrima8594]

Thanks a lot for the tutorial I am a beginner I need to learn more about Access

[@80Ichinose]

Your videos are great!

[@599CD]

Glad you like them!

[@80Ichinose]

Oh absolutely. I’m a pharmacist and I work on drug database information. Most I work with use excel but I decided to try to use access. So I’ve been watching your videos on how to do certain things! Very impressed with your delivery and methods.

[@StevenCantrell-i9r]

Thank you for the great video. I'm trying to have a database of pickup and delivery locations connected to a table of mileage numbers. When a particular pair of pickup and delivery locations appears, I want the mileage that it connects to in the other table to automatically populate. My pickup and delivery table has dozens of duplicates, and so that we don’t have to look up the mileage for that exact lane over and over again, I would like the primary table to look up the mileage from a second table. How do I connect many pickup and delivery location pairs in a table to one mileage number in another table?

[@599CD]

599cd.com/Ask

[@hubertz.6560]

great job

[@Yami524]

Hi Rick. I work for a restoration company we have several job types which are emergency service, repairs, owners request and mold remediation. I want to be able to enter for each customer if they have either 1 job types or they can also have many as a subform. Also I like that you have the contact subform on the customer form as well. Can I have those two as buttons on my customer form/

[@599CD]

599cd.com/QQ

[@darkshadowwolf3475]

Is their any way to have more than one foreign keys from another table. So basically, i am working on a rate sheet that would take two locations from the location table and it will have a specific rate for movements between these 2 location. The Goods delivery table would also have to have 2 foreign key to the Location table for Pick up location and drop off location. I am hoping to not have to create 2 tables for locations as the same locations would be in both tables since a job would start with the delivey of equipment and ends with the collection of the equipment.

[@alializadeh8195]

Thanks

[@joshuacarpenter5358]

Not me watching this video looking out my window at my 1994 Ford Taurus lol

[@599CD]

Haha. Funny thing was I recorded my very first Microsoft Access 101 video back in 2002 I believe and I re-recorded it four or five times since then and every time I just keep the old graphics and just put them in new slides. Sure I can redo the graphics but for me it's just a nostalgia thing. And hey they get the job done.

[@theecobazar6359]

Nice Video Sir...Thanks sir.............& Eid Mubarak ..............From Bangladesh - Dhaka....... Mirpur 13

[@599CD]

You're welcome.

[@garycurtis]

Excellent video, I have a question. For the many to many relationships, Do you use VBA code to update these tables simultaneously for the join table? Or do you update them one at a time using VBA or other means?

[@599CD]

No need for any VBA. Access maintains the relationships using the Link properties in the subform object. Watch 599cd.com/ManyToMany and 599cd.com/EventEnrollment

[@mechtechpotato4249]

Hello Richard, I am wondering if junction tables with more than 2 tables being joined supported on a SQL server. Thanks, Carson R.

[@599CD]

Yep, they should. It's just basic SQL joins.

[@mechtechpotato4249]

@@599CD Thanks Richard.

[@PJones43213]

Hi Richard, I have a table that contains all of my organizations employees and details. I then have a table that contains open positions and details. Additionally, on the employee table, where there are open positions, the employee name field just lists vacant or the job req. number if one has been assigned. When someone is hired and I fill their name into the open positions table, I would like the name of the hired person to be automatically filled into the employee table in the correct vacant position row. There are other details kept in the open positions table that do not need to be pulled into the employee table so I am just looking to pull the value from a specific row and cell. Is there a way for me to do this? Any guidance would be very much appreciated. -Patrice James from Columbus, Ohio

[@599CD]

This is just a matter of storing the EmployeeID in the position table. You can then refer to all of his other information via the relationship.

[@kennisrogers2585]

Is access pretty much an easier way to perform xlookups?

[@599CD]

Mmm... I wouldn't necessarily put it that way.

[@btechnical3252]

Where to Create Relationships? In the Front-End or Back ... Why we create relationship in front end... ( infact we can create these relations in back end very efficiently) If we must use front end for relationships... So how to create a validation relation in frond. To a list or to a table.

[@599CD]

I don't usually bother with global relationships unless I need referential integrity, which I usually don't bother with (I handle it in code).

[@usertgvh]

My question here is about relationships and setting up tables in a given situation. I want to build a database that has a table for people who work as artists as well as designers. They sing as well as design either alone or with others. Simple many-to-many table would have created an Artist table with ArtistID as PK and a joining table llike artist_Album with artistID and albumID as PKs (composite PKs) and an Album table with AlbumID as PK but since here we have another table designer with designerID as PK as well. How can we design in this situation? Please reflect onto this situation whence one has more than 2 tables to combine in a many-to-many relationships?

[@599CD]

Feel free to submit your question at 599cd.com/TH

[@chadthomson2169]

I am trying to create a query where i can select a work shift using a combo box (shifts are in a table), and then select the date range. It should then pull the number of items i have summed in 4 different tables. This has been killing me. can you help?

[@599CD]

599cd.com/Ask

[@d.thomas5357]

Is there a way to add a parameter field to a report header. For example, I want to add phrase Effective from June 2, 2021 t Thru [parameter field] where I type in a date.

[@599CD]

I would recommend using a form field for the parameter. Then you can reference it from anywhere in your report. 599cd.com/FormName

[@revision_classesTM]

👍👍👍👍👍

[@599CD]

:/

[@northmaineguy5896]

Ah yes, my father was born in Bridgeport, CT in 1925!

[@599CD]

Cool beans!

[@TomLewis-m4g]

You spent almost ten minutes explaining things with graphics instead of using the software you're teaching people to use. I watched the whole thing and you don't even teach how to create/manage relationships. Teaching has come a long way since the 90's.

[@599CD]

You need to learn the concepts and the WHY first before you get your hands dirty with the HOW. This is the best way to learn most things. Don't like my style? Plenty of other tutorials out there to choose from...

[@Sulz]

This is an introduction video to show why Access is used and what it means to be a "Relational Database". It is a great video.

[@599CD]

Thank you, @Sulz