Access 2016 for Beginners Part 21: How to Set Up Table Relationships

Get a FREE Microsoft Access 2019 Introductory Course here https://theskillstream.com/access-2019-intro-course
Get the complete 9-hour Access 2016 training course here https://www.simonsezit.com/courses/microsoft/learn-microsoft-access-2016-training-tutorials/
Get the complete 8-hour Access 2016 advanced course here https://www.simonsezit.com/courses/microsoft/learn-microsoft-access-2016-advanced-training-tutorials/
During this Microsoft Access 2016 training tutorial video, we will demonstrate how to set up two relationships. We will also talk about referential integrity and cascade delete records.
Check out some of our other training on YouTube:
Access 2016 training tutorials: https://www.youtube.com/playlist?listPLzj7TwUeMQ3jb4HGldAc307RUUCn2lX4F
PowerPoint 2016 training tutorials:https://www.youtube.com/playlist?listPLzj7TwUeMQ3jj_QkuckJNn8RddhwlQKOM
VBA for Excel tutorials: https://www.youtube.com/playlist?listPLzj7TwUeMQ3hWRi0mgxdyWkT0QaYKuBGZ
Excel 2016 training for beginners: https://www.youtube.com/playlist?listPLzj7TwUeMQ3jUeMoLReqNzzuKj7rdLhZ2
Project 2016 training: https://www.youtube.com/playlist?listPLzj7TwUeMQ3gPqakrFQ8fSNPu00rsOuzu
Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining
Facebook: https://www.facebook.com/SimonSezIT/
If you enjoyed the video, please give a "thumbs up" and subscribe to the channel ;-)
Welcome back to our course on Access 2016.
In the preceding section we started looking at relationships. And in order to set up relationships for the Esprit de Tour database I created a new table containing the types of trip that Esprit de Tour run and a link record, a record that’s going to link each trip with the types that that trip has. And in that way we’re building up indirectly a relationship between trip and type. Using the link record and in fact the relations we set up are from Trip to TripType and from Type to TripType. So in order to do this we actually set up two relationships.
The first relationship we’re going to set up is Trip to TripType. And the relationship is based on the ID in the trip table. So each record in the trip table, each trip has a unique ID. That’s the primary key. And records in the trip type table for that trip will have that same trip ID in the TripID field in the records in the trip type table. Now in order to set up the relationship what I do is to drag ID in the trip table and drop it onto TripID in the trip type table. And that brings up the Edit Relationships dialog where I can actually set up the details of the relationship between these two tables.
Now the first thing I do is to make sure that I’ve got the right tables and the right fields. So on the left, Table Query, Trip, ID, yep that’s the primary ID of the trip table records. That’s fine. And then TripType, TripID. That is the long integer field that I’ve set up to match the ID in the trip table. So I’ve definitely got the right tables and fields selected.
The next important decision is do I enforce referential integrity? Now on this occasion I’m going to say yes but I’m going to explain to you now what referential integrity is.
Let’s imagine a situation where I’ve created a trip and corresponding to that trip I’ve say created four trip type records. So there’s a trip type of beach and a trip type of family and a trip type of adventure and a trip type of escorted. And each of those has the appropriate type, beach, etcetera, and the trip ID for my selected trip. What would happen if I deleted that trip from the trip table? I’d finish up with four records in the trip type table that had no links at the other end of this relationship. They’d be sort of orphans. So those four records would be referring to a trip that no longer exists. Making sure that that doesn’t happen is called Enforcing Referential Integrity. And once I’ve checked that if I try to delete a trip what Access 2016 will do is it will make sure that there are no outstanding trip type records that use that trip. So it won’t let me break the database integrity in the way that I just described.
Now there are a couple of other options. Let me talk about one of them. A couple of options down below Enforce Referential Integrity, Cascade Delete Related Records. If I check this option if I deleted a trip record in the situation I just described Access would automatically cascade delete the trip type records for that trip. Now that of course would mean that the database wasn’t broken because those four records
Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.