|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Relationships By Richard Rost 
5 years ago
Relating Multiple Tables, One-to-Many, et al. 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. MembersMembers 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.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Links
See Also
Recommended Courses
Additional ReadingRelating your tables together is the fundamental backbone of building databases. It's what separates "modern" databases like Access from spreadsheets and "flat-file" databases of years gone by. Essentially, you want to make multiple tables in your databases to cut down on duplicate data. Duplicate data slows your database down and makes it inefficient (not to mention cumbersome to update and edit). Imagine having to store every customer's information with every order they place! Not too efficient.
So you make multiple tables, and you relate them to each other - usually with an ID or "key" field. For example, in this screenshot from my class, I'm showing students how to create two tables to relate drivers and vehicles. By moving the driver data to a different table, we've created a properly "normalized" database. Normalization is key to a good database. Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, relationships, 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
IntroIn this video, we will talk about how to create and use relationships in Microsoft Access, including how to relate information across multiple tables such as customers and the items they purchase. We will discuss the differences between one-to-one, one-to-many, and many-to-many relationships, the importance of primary and foreign keys, and how relationships help reduce duplicate data and improve efficiency in your database. You will also learn basic ways to display related records using forms and subforms, with real-world examples to help you understand how to organize your Access databases more effectively.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we're going to learn about relationships in your database, and how to relate data from different tables together in Microsoft Access. Today's question comes from Simone from Bridgeport, Connecticut, one of my platinum members. Simone says, I'm a realtor. I am 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 purchased. Once in a while, I get a customer who has purchased multiple houses. That happens. 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. Simone, you are correct. Most people are familiar with Excel, where you just put stuff in rows. But the power of Microsoft Access is that it is a relational database. You can have multiple tables that are related to each other. For example, in your case, you would have one table for people (your customers) and another table for the homes they purchased. This is one of the most important concepts to understand in Access once you move beyond the beginner phase. In my beginner classes, I primarily teach people how to work with single tables, build forms for them, reports, queries, and so on. But once we get into the expert classes and my Expert Level 1 class, I start teaching about relationships. Let me give you a general overview of how relationships work. Now, there is a very scientific and geeky definition for exactly what makes up a relational database, but for all intents and purposes, a relational database has the data organized and can be accessed according to relationships between the tables. For example, you've got customers and houses they purchase. You've got customers and cars. You've got orders and products. Different tables that are related together. Relational databases help to minimize errors, increase efficiency, and eliminate duplicate data. You don't have to have your customers' information in each record if you have their house information in that same table. You would have to copy their information. Let's see how that works. Okay, so in our database, we're going to be tracking customers and the vehicles they own. Here's Joe Smith. He's got one car, a 1995 Ford Taurus. Don't laugh at the year. I copied all these screenshots from the very first time I covered relationships. Way back, I think 2002 was the first time I did this, and I've been using the same screenshots every time. This is probably the fourth or fifth time I've redone this lesson. But the screenshots are valid. I live in Florida, so sometimes you see 1995 cars on the road down here. So anyways, you're tracking this, let's say, in Excel, or you've got one table in your Access database. You've got Joe Smith's full number, address, and then his car. No problem. Now Joe Smith buys a second car. You've got to copy Joe Smith again, his full number, same address: 1997 Ford Escort. Do they even make those anymore? I don't even know if those are still on the road. So now he's got two cars. Third car. As you can see, we've got a lot of duplicated information. Now I add his buddy, Bill Jones. Bill's got two cars as well. So you can see there how a lot of information is duplicated between records. Now, if you want to change, let's say Joe Smith's address, you've got to make sure you change it in three different places. The exception, of course, is if you want to track something that is important at the time. For example, where an order was shipped. That's an exception. I cover that in my expert classes too. If you want to put on an order, for example, the customer's address at that time, that's fine. But if you just want Joe Smith's current phone number and address, this is a lot of wasted duplicated information. The better way to store this information is to use two tables in what's called a one-to-many relationship. Here we have one customer to many vehicles. Joe Smith's information shows up there just once, and the vehicles are each in their own table. How do we know which customer owns which vehicle? We use a customer ID. That's the primary key for the customer table. Joe Smith's customer ID is 101. Then we'll use that 101 in the other table, the vehicle table, to track who owns each vehicle. See how that works? Each of the other customers has their own unique customer ID. Bill Jones is 102. Sam, I guess that's Sam Price. I don't think I caught that when I first recorded this and it just kind of stuck. Sam Price is 103. You can see by looking at the vehicle tables now who owns what car, and we can relate that back to the customer information. We don't have to copy all of Joe Smith's information onto each car record. Now we've got two types of key fields: there's a primary key and a foreign key. You don't really have to remember these terms. Remember 'primary key,' obviously. 'Foreign key' is the primary key in the other table. Let me show you. Here you can see, with the little key symbol, the primary key for each of those tables. The primary key in the customer table is that customer ID: 101, 102, 103. Since that's the primary key in the customer table, it becomes the foreign key in the vehicle table. You can see that key right here. That indicates what customer owns each of those cars. That's the foreign key. Why is it a foreign key? Because it relates back over to this table. Now the vehicle table has its own primary key. If it's an autonumber, it just starts at 1, 2, 3, and counts up. So this 2003 Ford Focus, for example, the primary key is vehicle number 3, and it belongs to 101. So that's Joe Smith. See how the relationships work back and forth between tables? There are three basic kinds of relationships. There are more, but these are the three you have to worry about when you're getting started with relationships: one to one, one to many, and many to many. By far, one to many is the most popular type. Let's talk about all three of them. One to one is when you've got one record in one table and only one matching record in another table. For example, let's say you've got one payment method for each customer. You don't want to have multiple payment methods. It's just one. Joe Smith and there's his payment method. But you want to keep the payment methods in a different table, maybe for security purposes. You can also do this. I do this a lot with what I call extended customer information. Let's say you've got your primary customer table. It's got all your customers, phone number, address, that kind of basic stuff. But then you've got other information, maybe some random questions you don't ask everybody, you don't get them for everybody: shoe size number, children, favorite pizza toppings, those kinds of things. You'd put that in a second extended customer table and you'd only have one set of responses for each customer. One to one is actually kind of rare. I don't use a one to one relationship that often. One to many is the big one. You have one customer, many vehicles. One customer, many contacts. If you want to have multiple phone numbers or multiple addresses, you can put those in separate tables. If you want to have four different phone numbers available for a customer, you can do cell phone, home phone, work phone, and so on. Put that in a second table. That way, you're not limited to just three phone number fields in your primary customer table. You could do vendors to products: one vendor, he sells multiple products. A many to many relationship is a little more complicated. I cover this later on in the expert series. Again, not as common as a one to many. Many to many indicates that many people can drive many cars, but each car can also be driven by multiple drivers. See, it's a little more complicated. Another good example is students and classes. Each student can enroll in multiple classes at a college or whatever, but each class can have multiple students. In order to properly do a many to many relationship, you need a third table called a junction table. Here, for example, you can see that customer 101, Joe Smith, can drive cars 1 and 3. Car 1 is the Ford Taurus. Car 3 is the Ford Focus. But you can also go backwards. You could say car 3, the Ford Focus, can be driven by 101 and 102. See, there's a 3 here and a 3 there. And it can be driven by both of these people. So it's a many to many relationship. It goes in both directions. That's a lot more complicated. Don't worry about it. I cover this in one of my expert classes. I'll be doing a TechHelp video on many to many relationships, too. It is something I get asked quite a lot. I have whole courses on it, but I like to give you all the general overview here in the TechHelp videos. Here's another example. In my TechHelp database, for example, I have customers and contacts. Now, a contact is every time you talk to somebody. So you can see right here, customer ID number 1, me. And right here you can see customer ID. There's the foreign key. One and one, that's me. So, contact 1, contact 2, called about a job, came in for an interview. The next contact is customer ID 2. That's James Kirk, and he fought Klingons. So you can see how the relationships between these two tables work, just by setting that ID. There are generally two ways to show a parent record with the related child records. For example, we've got customers and contacts. Customers, the parent table; contact is the child table, the related table, the many side of a one to many relationship. You can either open a form and find specific data, and you can do that with the control wizard, or you can use a subform. I show how to do both of these things in my TechHelp blank database video, which I'll put a link down below. Go watch that. It's very good. I'll mention it again in a few minutes. Open a form and find specific data. So, you've got the customer form, and on the customer form, you've got a button here to open up the contacts. The contact form opens up showing just that customer's contacts - not all of them, just the ones for this person. The second way to show related records is to use a subform. Here, I embedded that contacts form inside of the parent customer form. Here's the TechHelp free template again, free download on my website. You have your customer list. Open up a customer; here's the customer information, that's the parent record. Open up contacts and you'll see there's the related contacts with just this customer. If you go to a different customer, Jim Kirk, hit the contacts button: fought Klingons, his contacts. One to many. Each customer can have many contacts. There's also the customer with contacts subform. You can go through the different customers down here and see each of them have the option to have multiple contacts. They don't all have contacts in here. In this contacts table, it's hidden here - you don't necessarily have to see the ID here, but it's stored in the contacts table. The customer ID 3 goes in there. That relationship is actually managed by the subform itself, and again, I show how to do all of this in my other free videos. I'll put links down below. Here are the videos. One is open a form and find specific data. That will allow you to open a second form and show related values, or you can use subforms. I have videos for that too. I'll put links down below. Also, make sure you go watch my blank template video. It shows you how I built that TechHelp free template from the ground up. The second video in that series goes into making the customers with contacts, a very popular video, and it talks a lot more about relationships. If you want the full, in-depth relationship lesson, Access Expert Level 1. Take my beginner course first. There are nine levels of that, then Expert Level 1 - relational database concepts and a lot more detail: primary and foreign keys, types of relationships, ad hoc query joins, relational combo boxes, all kinds of stuff. That's all in Access Expert Level 1. Of course, I've got a full relationship seminar. As you can see, relationships are a pretty in-depth topic. I just scratched the surface in this video. I cover all the different types of relationships, including something I call a self-join. That's relating a table to itself. That's good with family relationships, for example: a person to their mother, their father - those are other people in the same table. It's just got one table; it's got both the primary and the foreign key in it. Plus, you can do employees to supervisors. Again, they're both employees, a supervisor is a type of employee, and the table relates to itself. There are all kinds of different relationships I cover with them in detail in my relationship seminar. If you want to learn more about relationships, for the members in the extended cut, I will talk about self-joins, global relationships (that's setting up relationships on a database level), referential integrity (that's preventing child records from existing that don't have parent records - you can't have an order without a customer, for example), that's an option you can turn on or off. Then we've got cascade updates and deletes. That's if you delete a customer, it also deletes all of his contacts. That can be dangerous. We'll talk about that more, but that's in the extended cut for the members. Silver members and up get access to all of my extended cut videos, and gold members can download the databases from the TechHelp templates. How do you become a member? Click the Join button below the video. After you click the Join button, you'll see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. These free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You'll see a list of other videos, additional information related to the current topic, free lessons and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you'd like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is a primary advantage of using Microsoft Access over Excel for tracking customer purchases?A. It provides better support for relational data and eliminates data duplication. B. It allows you to create more colorful charts. C. It automatically sends emails to your customers. D. It doubles the speed of data entry compared to Excel. Q2. In a relational database, how should you model customers who may own multiple vehicles? A. Store all vehicles and customer details in a single table, duplicating customer information for each vehicle. B. Use two tables: one for customers and one for vehicles, relating them together. C. Store all vehicles in one table, but customers in multiple different tables. D. Use Excel instead of Access for simplicity. Q3. What is the purpose of a primary key in a database table? A. To uniquely identify each record in the table. B. To secure the table from unauthorized access. C. To store the main value of the table. D. To describe the relationship between tables. Q4. What is a foreign key? A. The primary key of another table used to create a relationship. B. A field with values from another country. C. A field that stores the language for each record. D. A duplicate record in the table. Q5. What is a one-to-many relationship? A. One record in Table A relates to many records in Table B. B. One record in Table A relates to one record in Table B. C. Many records in Table A relate to many records in Table B. D. Many records in one table are unrelated to another table. Q6. Which kind of relationship is most common when designing databases in Access? A. One to many B. Many to many C. One to one D. Self-join Q7. When might you use a one-to-one relationship? A. When you want to store extended information for some customers in a separate table B. When each customer has multiple vehicles C. When you want to relate vendors to multiple products D. When you need to store addresses for several customers Q8. What is required to set up a many-to-many relationship in Access? A. A third table called a junction table B. Storing everything in one table C. Creating multiple primary keys in one table D. Using only foreign keys in both tables Q9. In a customer and contact scenario, which table is considered the parent and which is the child? A. Customer is the parent, contact is the child B. Contact is the parent, customer is the child C. Both are parent tables D. There is no parent or child relationship Q10. How can you display related child records for a parent in Access forms? A. Use subforms or open a separate form filtered to related records B. Only display parent records and ignore children C. By printing both tables and comparing them manually D. By using Excel to organize data Q11. What does referential integrity ensure in a database? A. Child records cannot exist without matching parent records B. All records must be stored alphabetically C. Tables are always visible on the database window D. Only numeric fields can be related Q12. What is a self-join in database relationships? A. A table relating to itself, using both a primary and a foreign key in the same table B. A table with duplicate data C. Two unrelated tables D. Joining two databases together Q13. What happens if you enable cascade deletes in a relationship? A. Deleting a parent record will delete all related child records B. Nothing changes; child records remain C. Only the parent record is deleted, child records stay D. The entire database is reset Q14. Why is it important to avoid duplicating customer information for each purchase? A. To minimize data entry errors and make updates easier B. To make tables look longer C. To make record searches slower D. To access the database outside Microsoft Access Answers: 1-A; 2-B; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone is all about understanding relationships in your Microsoft Access database and how to efficiently connect data across different tables.Simone, who is a realtor, sent in a question about managing customers who purchase multiple houses. She has been using Excel for years, but is now setting up her first Access database. She wondered if she should make a separate entry for each house in her customer table, noting that this would create a lot of duplicated information. Simone is right to be concerned about duplication. Many people coming from Excel are used to keeping everything in a spreadsheet, where each row holds all related data. However, Access is designed to be a relational database. This means you can set up multiple tables and establish connections between them. In Simone's case, she should have one table for her customers and a separate table for the homes they purchase. This is a fundamental concept in Access beyond the basics. In my beginner classes, I focus on building tables, forms, and queries using a single table, but as we move into more advanced material, particularly Expert Level 1, we explore these critical relationship concepts. To understand how relationships work, imagine you're tracking customers and the vehicles they own. Joe Smith purchases a car, and you store his details along with the car information in a single table. So far, so good. But what happens when Joe buys another car? You would have to create another record with all of Joe's details again, leading to unnecessary duplication. The problem becomes even bigger if you have more customers and more cars. Changing Joe's address, for example, would require updating multiple records, which is error-prone and inefficient. The only valid reason for duplicating data like this is if you need to record a detail specific to a moment in time, like an order shipping address. The better solution is to use two tables linked in a one-to-many relationship. The customer table holds Joe Smith's information just once, and the vehicles table lists each car tied to a customer through an ID. Each customer receives a unique customer ID, used as a foreign key in the vehicles table. Joe might be ID 101, and that ID appears beside each car he owns in the vehicle table. The vehicle table has its own primary key as well, usually set to autonumber. This approach avoids repetitive data and keeps updating records straightforward. It's important to understand the two main types of key fields you'll encounter. The primary key is a unique identifier for records in a table, like the customer ID. When this primary key is referenced in another table, it's called a foreign key. This is how tables are linked together. For example, in the vehicles table, the customer ID is stored as a foreign key to indicate ownership. There are three basic types of relationships that you'll encounter in Access: 1. One-to-one: One record in a table corresponds to exactly one record in another table. This is rare, but you might use it for storing specific information like a customer's payment method in a separate secure table, or for optional extended customer details such as favorite pizza toppings or shoe size. 2. One-to-many: This is the most common type. Here, one customer can have many related entries in another table, such as multiple vehicles, phone numbers, or addresses. You might also use this relationship with vendors and products, where one vendor can supply many products. 3. Many-to-many: This relationship is a little more complex and needs a third table, known as a junction table, to work properly. For example, students and classes form a many-to-many relationship, as each student can enroll in multiple classes, and each class can have many students. Similarly, many people could drive many different cars. The junction table holds pairs of IDs to connect records from both tables. This topic is covered in detail in my expert classes and is something I see a lot of questions about. To help visualize relationships, consider the example from my TechHelp database, where customers are linked to contacts. Each customer can have multiple contacts, such as every time you interact with them. The contacts table stores the customer ID as a foreign key to tie each interaction back to the correct customer. When displaying records, there are generally two methods for showing a parent record with its related child records. The first lets you open a form that displays only the related records for a specific parent. The second method uses a subform embedded within the parent form to automatically show related records beneath the parent details. My free TechHelp blank database video covers both techniques in detail. The subform approach is especially useful for one-to-many relationships. For example, you can move through your customer records and see at a glance all the related contacts for each customer. The relationship between the tables is managed behind the scenes, so there's no need to manually enter IDs each time. For those wanting a deeper understanding, my Access Expert Level 1 course goes far beyond the basics, covering relationship concepts, primary and foreign keys, different types of relationships, ad hoc query joins, and more. I even have a relationship seminar that explores advanced topics like self-joins, which are relationships where a table is related to itself. This is useful for tracking relationships like employees to supervisors or familial relationships using only one table. For members, today's Extended Cut covers self-joins, defining global relationships at the database level, and enforcing referential integrity, which ensures you cannot create a child record without a corresponding parent. You will also learn about cascade updates and deletes, which automatically update or delete related records, though you need to use these features with care to avoid unwanted data loss. Silver members and higher get access to my extended cut videos, while gold members can download the sample databases and access additional resources such as my code vault. Platinum members receive access to all my beginner and select expert courses, not just for Access but for Word, Excel, Visual Basic, and more. If you have not tried my free Access Level 1 course, it's available on my website and covers all the essentials of building an Access database. Level 2 is just a dollar or free to channel members. I welcome your questions for future videos, so check out my TechHelp page for details on how to submit your own. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListOverview of relational databases in AccessReducing duplicate data with table relationships Setting up one-to-many relationships Using primary keys in tables Using foreign keys to link tables Examples of customer and vehicle tables Explaining one-to-one relationships Explaining one-to-many relationships Explaining many-to-many relationships Using a junction table for many-to-many relationships Examples of students and classes relationships Displaying related records using forms Displaying related records using subforms Parent-child table relationships in Access Linking forms to show related records Embedding subforms to display child records |
||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access relationships, 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 PermaLink Relationships in Microsoft Access |
