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:

BECOME A MEMBER:

LEARN MORE:

LINKS:

COURSES:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

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.

[hsmith]

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!

[ancestrycounts]

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.

[xm_tech]

Thanks for sharing this video. It’s very helpful

[ishmaillibbie]

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.

[xlschool]

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

[kurosakibankai]

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

[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?

[PJones]

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

[mechtechpotato]

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

[darkshadowwolf]

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.

[dniezby]

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

[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?

[theecobazar]

Nice Video Sir...Thanks Eid Mubarak Bangladesh - Mirpur 13

[chadthomson]

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?

[btechnical]

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.

[kennisrogers]

Is access pretty much an easier way to perform xlookups?

[d.thomas]

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.

[joshuacarpenter]

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

[northmaineguy]

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

[yvwejir]

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.