Microsoft Access 2016 Tutorial: Linking the Tables and Defining the Relationships

This is part 7 of a multiple part series of the Access 2016 tutorial. This session focuses on concepts related to linking Access tables in a database and properly defining the relationships. Learn more at learn.kaceli.com
In more detail, this session we will focus on:
- Understand what relational databases are and how to link the tables in the database.
- Understanding the use primary and foreign keys in Access 2016.
- Learn how to define the relationships between the tables.
- Learn how to save the defined relationships between the tables.
- Explore how to run a query from the linked tables in Access 2016.
If you like the video, please "Like," "Favorite," and "Share" it with our friends to show your support on this work. Subscribe to this channel so that you do not miss out on the new videos on this series.
Video URL: • Microsoft Access 2016 ... . The full tutorial is coming soon. Stay tuned. Access 2016 Part I is here if you want to start from the beginning: • Microsoft Access 2016 ...
Link to the working files for the tutorial: kaceli.com/a/access2016/
It is suggested that you continue to part 8 after completing this module.
Keep in touch and please spread the word by subscribing and using the share links below the video and commenting:
Website: kaceli.com
KZbin Channel: / skaceli
If you want to take get a deeper understanding of Microsoft Access, take a look at learn.kaceli.com
Copyright notice: These videos may not be downloaded or distributed in any way without permission from Sali Kaceli. Contact me if you have such a need.

[@lani4ushkata46]

In 11 minutes this man taught me what my university tried to teach me in 20+ hours

[@foxptnetwork898]

I can't stop watching every steps of your tutorial. Hats off.

[@garyhodgson3042]

Thank you so much. Your instructions are clear and enthusiastic. They help me understand MS Access. I have put into practice what I have learned from your videos and I am now up and running with a nice little data base. Again thank you.

[@KaceliTechTraining]

Great to hear that Gary. Sorry for a late response. Hope the database has been running well.

[@lukejames5467]

Thanks Sali, this was really helpful and well explained. 👍

[@marissa954]

Thank you for your hard work!

[@Mehrdad995GTa]

Complete And Clean Thank you very much

[@user-mg4zt1qr3r]

Saved my day!!! Thank you so much!!!

[@carleewalsh5502]

Very helpful! Thank you.

[@seus2731]

oh I wish I found this video b4, you make access look so easy, THANK YOU!

[@enassrefai2475]

same man

[@Ladyboston]

Thank you for making videos regarding this database

[@taaskeprins]

Great vid, Thanks for the effort!!!!

[@michaelmoschides5368]

GREAT SESSION. GREAT INSTRUCTION. THANK YOU.

[@KaceliTechTraining]

Glad it was helpful!

[@bensonmophakedi9159]

great education.. slow and precise... saved me a lot of frustration

[@KaceliTechTraining]

Thank for your feedback. Glad you liked it. Please press 'like' on the video and share the link in your social media if you can. Subscribe if you would like to be alerted for new content.

[@betelhemfikadu3885]

simple and easy !👌

[@PempheroSiliyatheMrEngineer]

Thanks alot... Well taught.

[@TECLOVENIA]

I really appreciate your help bro 🙏

[@rahulberry4806]

nicely explained

[@kaloyangeorgiev1415]

evala kiopek spasqvash zadnika mi i tozi na moite averi! Greetings from Bulgaria!

[@andreabelhadj9129]

Thank you! you explained it brilliantly with clarity. Now all is clear

[@temesgenfuli3831]

realy thanks it is clear

[@arushi8141]

Thank you :)

[@papapiepatonie915]

thank you good sir

[@thebeat8184]

Thank you 🤝

[@user-eb9hk9mr3t]

thank you so much

[@hildakabareebe2833]

THANX TO KACELI TECH TRAINING

[@allanwilson1123]

That’s good quality content!!!

[@elliotcampbell8572]

ano man absolute mad lad uploaded this

[@kirkwatson5603]

Well good

[@allanwilson1123]

Kirk Watson this man is an absolute unit

[@allanwilson1123]

Elliot Campbell this really helped my crippling depression

[@elliotcampbell8572]

👌

[@patb8985]

Thank you

[@dm7628]

Thanks 🙏

[@christiangrey6258]

Can you do this linking of tables to define relationship in order to pull data (create queries) ?

[@shanewilson3653]

9:00 thank you for a critical point often missed

[@irina3107]

Good day, thank you for the video. Tell me please how to modify the link between the two tables? For example here are the tables: Table1: Insured; Table 2: Dependent. And this is what I was asked to do: Modify the link between the Insured and Dependent tables to also include Insured who do not have any dependants. But I don't have any fields in those tables that have this name: Insured who do not have any dependants. How to do that? How to modify the link between those 2 tables? Do I need to add new field to both those tables ( The new field with the name: Insured who do not have any dependant)? Thank you so much

[@fomilindari9599]

Thank you so much Sir. Your instructions are clear, really helpful and well explained.

[@KaceliTechTraining]

Thank you. Glad you found it helpful. Please like the video and subscribe to the channel if you can.

[@ChrisSmithFW]

Love your tutorials but up until this one at least, unless I overlooked it, there has been relatively little mention about styling column header names. I could make assumptions but there doesn't seem to be consistency in the tables created for the tutorial (but this could be due my lack of knowledge). For example, in this tutorial as well as at least a couple of others before, in the customers table you show "CustomerID" but in the contracts table you show "Customer ID". Does this matter? Which is accurate? Do I need to create table column headers with or without spaces? Do they need to be consistently styled? Thanks so much and except for this minor confusion, your tutorials are great. Thanks for taking the time to do them.

[@KaceliTechTraining]

The spacing in the column names does not matter. The data type for the actual field matters. Otherwise they will not link properly. For that matter, you could call it CID and it is fine, as long as you are entering the actual customer's id in that field.

[@ChrisSmithFW]

@@KaceliTechTraining Fantastic. Thank you so much. Really appreciate you taking the time to explain.

[@HorridOnlineTroll]

THANK YOU SO MUCH T_T

[@James-ip5gz]

2x speed. Thank me later.

[@TheGoodLifeProductions]

Thank you

[@stunity1767]

Thank you!

[@1sidooo]

شکرا حبیبي

[@warmlandmobilebrakes]

You're doing God's work

[@Daniel_Leith]

Thanks

[@ifigotitright3012]

Life saver

[@boakyeyiadombismark2093]

thanks

[@___llvm]

Thnx DUDE

[@soranrmohammedtaha8393]

I recommend everything

[@KaceliTechTraining]

In that case, please press 'like' and click on the little bell icon to be alerted for new videos. That really helps.

[@masgitz]

Do you know if If these relationships carry over into SharePoint connected tables?

[@deantwaunbalton1177]

Anyone know how to make a relationship say one-to-many instead of one-to-one? Help is greatly appreciated

[@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.

[@angelsapphire6088]

So if you were to make changes to Customers (like update their name), would that change reflect on the other two tables since you created a relationship with them?

[@KaceliTechTraining]

Yes, because the linked tables will reference the data in the Customers table.

[@jasonel28]

Thank youuuuuuuuuuuuuuuuuuuuuuuu

[@mweembamalundu619]

Ohhhh my Gàààààd, I almost forgot to like the video. That was going to be so unappreciative. Great video 👍🏿

[@DemonSwrd]

what to do when you have a combo list that you want to input through a form but it says the join key isnt in the recordset?

[@nicksonisiebea9852]

Hi Sali Kaceli is there any way you can perform calculations in MS access?

[@KaceliTechTraining]

Yes, you can. See this video: kzbin.info/www/bejne/jKLPpqBjhsqigsk

[@sallu21m]

how will the data entry forms work in linked tables?? will Making entry in to one table in common filed between linked tables auto update the entry into the same field in the other table ?? could you pls clarify ...thanks

[@KaceliTechTraining]

The data you enter in the form will be entered in the corresponding table where the field is linked to. Then the corresponding linked fields will be updated accordingly i.e. when you pull up the data, the newly entered info. will show up. e.g. if you update the address for customer A in the customer's table, and that customer is referenced in table in the orders table, then when you run a report or query referencing customer A's address for new orders, the new address will show up.

[@admissionsicsatlanta860]

I am not getting the window "How do you want to view your data". I've searched and it has something to do with the primary key selection, which I think looks fine. The only option I'm seeing is a one-to-one relationship and what I want is a one-to-many, but am not able to change it. Any pointers are appreciated. TIA!

[@sekterdxd8654]

finally good mic

[@ihsanaadil5760]

I have a data of spare parts and i have to assign multiple spare parts to one buyer , in excel i do it by merging cells , but i don't know what i have to do for that in access , Do anyone know ??

[@maximnaulaers5724]

Hello, I am struggling with the email add on in access. I am building a stock inventory and once a certain stock amount is below the minimum quantity, I want it to send an email to inform someone to order new items. But Access always sends the email double and I don't know why, are you able to help me?

[@martinc3907]

May I suggest you avoid using spaces in your field names. Richard Rost advises against using them as they can cause problems and in SQL you have to remember to use square brackets when referring to them. I just went through a painful process of updating over 1,000 rows in a lookup table with Python which like any computing language thinks these field names are two variables. Instead use camel notation for example "InvoiceNum" or Hungarian mixed notation "Invoice_Num".

[@KaceliTechTraining]

Yes, agreed.

[@davidwade3905]

How do i call on a database and deploy ALL the information stored? Say when i pull up a name, it also gonna automatically deploy all the info that was stored, e.g: name, address and cell number?

[@polyflexzypatrusm6532]

It can be done using a query and the will be run by a macro

[@AbdurRahim-ot5gp]

A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control how to fix

[@jmike1373]

Is it possible to do this in excel? It appears to have something similar in excel 2016. I prefer doing it in access, but I need it for my job, and everyone is most familiar with excel. Thanks.

[@KaceliTechTraining]

You could probably use VLookup etc. in Excel but not in the same manner. Access is quite a bit more powerful when it comes to working with data particularly for queries or reports.

[@jmike1373]

Thank you. I ended up finding a simpler solution and integrated it into our process at an Amazon delivery site. Definitely ended up increasing productivity. It's not the best for data integrity, but I had to put the data in two separate tables, order it AtoZ by what would be the primary key, then copy and paste the info I needed. It worked out, but of course if the data were larger, I would need this method and prefer Access, but in order to integrate into the work process and train everyone, they would prefer excel, even though access is best.

[@nevillemills2600]

I have 276 Items which I want to create multiple records for. I cannot create the relationship for some reason.

[@ashimarqui4800]

When I try to link my relationships, my show table option is missing. I don’t know how to add it or find it. Can someone help?

[@donharrold1375]

I've got 4 tables. Each table has between 4 and 200 records. When I run a query it creates 1000s of duplicate results. There are no duplicate records in the individual tables. I don't know what's going on?

[@M3Bella]

Review the query, review relationships and redundancy

[@odinmaster6677]

is this what you call SQL relation ships

[@bryanstark324]

Type mismatch in expression? Anyone know why it's giving me that error?

[@geekmeee]

I would suggest Googling the error. When this error happened to me, I found out I was trying to link two columns that had two different Data Types that I was attempting to link together. The Data Types must be the same when linking two columns.

[@GulumbaKingsley-fg2bz]

👍

[@simi1528]

Is this for Mac users as well

[@NexGenSlayer]

You probably researched this by now, but Access is not available on Macs currently.

[@jessica33313]

Would need to do a virtual machine.

[@SteveMartinUSA]

This is so incomplete. How do you read the notation? Which table has the foreign key, and which has the primary key by looking at the relationships? It's hard to see what you're doing, is this it: Customer --> Invoices --> Contracts??? A better example would be to resolve a many to many relationship through the introduction of an intersection table: Customer Items.

[@dariyanb7801]

I'm panicking because mine shows no Design key anywhere in the software to create relationships. It shows "Relationship report, add tables, all relationships" and that's it. Every tutorial I see it comes up on screen for them as a pop up and I don't have that.

[@KaceliTechTraining]

What version of Access do you have? Are you seeing the tables on the left?

[@kirkwatson5603]

Some boy x

[@Riemax_]

Hey sorry if I'm kind of stupid but I took a screenshot and I'm trying to make it my background could you help me out love your channel

[@KaceliTechTraining]

Save the screenshot as a picture. Then open the picture, and then right click on it. Then choose set as background.

[@Riemax_]

Sali Kaceli how do I save the screenshot as a picture

[@leshunter5972]

xTitanWarfar3x I

[@aseprohmanudin4034]

Sali Kaceli b

[@polyflexzypatrusm6532]

Please can I send you a mail? I'm stuck and I could use your help

[@TheOperatorKB]

PUT ON 2X SPEED.

[@nemanjar]

it still does not work?

[@MetaferAsefa]

I am gread 11 student

[@azamrazaazamraza2865]

M͜͡

[@jamesreese7369]

You were going about 3X too fast. Please slow down in future videos.

[@SatyabratSwain1]

Thank you