Microsoft Access A to Z: Designing Table Relationships

In this short video we'll examine table relationships in a number of different Access applications including the classic Northwind database. Proper table relationships are foundational to data accuracy and peak database performance. We'll define how one-to-many relationships work with the primary key field and foreign key field, discuss one-to-one relationships, and also show how to create many-to-many relationships using junction tables. We'll also examine Lookup tables, and how they help define and constrain values for a field.
All of my KZbins can be found at: learncs.w3spaces.com/
Thanks, Lisa Friedrichsen

[@bluesmaster1005]

WOW! This is the BEST explanation of the "SO WHAT" for Access. I've been having a difficult time trying to figure out the Relational aspect, particularly in regards to the "Normalization" of the database. This is FANTASTIC.

[@lfriedrichsen]

Well thank YOU for taking the time to leave such a positive and encouraging comment. Sincere gratitude is highly underrated!! :)

[@Corbandallasyo]

I agree. Your videos are a fantastic resource for those of us who are just starting out. Thank you very much for taking the time out of your day to put these together!

[@Aixatem]

I've been watching this video to understand access database better for my class. Now I'm giving my exam on it. Thanks to you, I feel more confident about it!

[@lfriedrichsen]

Great to hear!

[@greenhoodie]

This was very well explained, thank you. This has helped me understand the depth of thought needed when planning out a db before you even start!

[@emmieHD]

Great video! It explained relationships clearly and concisely with great examples that answered the question I had. Thank you!

[@lfriedrichsen]

You're very welcome!

[@zachbiderman1115]

Lisa you are making me better at my job and I appreciate you!!!

[@lfriedrichsen]

Thank you! See all of my KZbins organized at learncs.w3spaces.com

[@FirstLast-cb2jr]

Another excellent video: I’m glad That KZbin found you for me!!

[@lfriedrichsen]

Tysm. See all of my KZbins organized at learncs.w3spaces.com

[@harrynickel8691]

Thanks. This video helped fill in some holes.

[@lfriedrichsen]

Welcome!

[@aftabsmerchant1]

We are looking forward for more on Acces from you, Especially explain the making of Northwind

[@lfriedrichsen]

I just started a new playlist based on the northwind database!

[@lfriedrichsen]

See all my youtubes organized at learncs.w3spaces.com/

[@s9malik]

thank you so much this helps me a lot 💖🤝

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@s9malik]

@@lfriedrichsen Okay

[@coldguy]

This guy is legend

[@lfriedrichsen]

Ty! See all of my KZbins organized at learncs.w3spaces.com

[@coldguy]

Thanks

[@moodkn]

Great way Thank you Madam!

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@kawtheralateeq8264]

Thank you .. ❤️❤️👍🏼

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@sheikhraisulislam4483]

thank u mam. it was really helpful

[@lfriedrichsen]

You are welcome! See a listing of all of my KZbins at learncs.w3spaces.com

[@erinkavanagh3553]

Thank you

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@kunxuan92]

Thanks so much for the succient yet clear explanation, i want to study the Northwind database, and checked the link, but it does not work, i could not download the file. can you please kindly share the file with me? 🥰

[@lfriedrichsen]

Thanks! This web page tells you how to download the Northwind database through the templates feature. learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases

[@mividalocastudio9366]

Amazing video. However, I have a question... How is the ShipperID (primary) key in Shippers table linked to a ShipVia as a 1 to many relationship in the Orders Table, when the ShipID and ShipVia names do not match? I thought they always had to match like SupplierID (primary) in Supplies Table to SupplierID (many) in Products Table. What am I missing? LOL

[@lfriedrichsen]

Very observant! The fields used in a one-to-many relationship do not have to have the same field name as you pointed out. It is common to give them the same name, but not technically required.

[@mividalocastudio9366]

@@lfriedrichsen Oh ok that’s very cool. Thank you for sharing your knowledge!

[@tOnK666]

I was about to ask :) thanks

[@easyspeak101]

I thought the middle link table was the 1-side of the other two connecting tables. Creating effectively two joined many:1 tables that make up a many to many relationship.. ?

[@lfriedrichsen]

No. If two tables have a many-to-many relationship then you model that with a junction table that is on the MANY side of a 1-to-many relationship with the two original tables in a many-to-many relationship. Go to the 4:12 minute mark in this KZbin to see how many-to-many relationships are resolved with a junction table. Products and Orders have a many-to-many relationship that is resolved with the Order Details table. One product can be ordered many times and one order can have many products. Note that the Order Details table is on the MANY side of the relationship to both Products and Orders. Another example is the Customers and Shippers tables. They too have a many-to-many relationship that is resolved with their one-to-many relationships to the common Orders table.

[@deholovi]

I'm stuck with sub datasheet view! for example, I've 3 separate tables. wanna relate them with one junction table. say, number 2 table has a common field ( could be described as the master field ), which is related to two other fields. one from number 1 table and another from number 3 table. after creating the relationship, when u press the " + " sign of the sub datasheet view, " insert sub datasheet " dialogue box appears! why so? I just wanna see related fields to that particular field. why that doesn't happen?

[@lfriedrichsen]

Subdatasheets show the "many" records related to "one" record in the table you are viewing in Table Datasheet View. So only the table on the "one" side of the relationship will have the ability to show a subdatasheet.

[@lfriedrichsen]

If you're getting the "Insert Subdatasheet" dialog box it means that you're viewing a table on the "one" side of a "one-to-many" relationship to more than one other table...so you have to choose which table's records to view in the subdatasheet.

[@deholovi]

thank you for replying

[@kawtheralateeq8264]

If I have questions where can I ask you ?

[@lfriedrichsen]

If it's a short question that I can answer here, I will.

[@johnnychin9598]

i need help, i have item say tshirt size small, but the price varies with different customer. say customer A, the tshirt small is US2.0, IF customer B the price its US 3.0, and tshirt medium for customer A is US4.0 AND US5.0 FOR CUSTOMER B. how do i make the table for this scenario

[@lfriedrichsen]

You'll need a Sales table with a field for the actual price per sale. Customers table has a one-to-many with Sales. Inventory table has a one-to-many with Sales. And you can even have a suggested/regular sales price field in the Inventory table. But the Sales table must have a field to record the actual price given you're telling me that the price may be different for each sale.

[@lfriedrichsen]

My KZbins on table relationships should be of value here. All of my KZbins are organized at learncs.w3spaces.com

[@usmanyousaf9102]

i want some information about Ms access

[@lfriedrichsen]

www.microsoft.com/en-us/microsoft-365/access/?rtc=1

[@sureshsivaram4689]

Your pace is too fast for learners.

[@lfriedrichsen]

You can pause it, rewind it, or watch it over again. You are in control of the pace.

[@nabilxgmail]

Great video thank you

[@lfriedrichsen]

Ty! All of my videos are organized at learncs.w3spaces.com