Microsoft Access A to Z: Relating tables

hello world this is lisa fredrickson your friend and computer science professor with another short screencast about access and in this exciting screencast we're going to relate tables to make a relational database if you've been following my series you know that there's two screencasts that come before this one that talks about access in an overview and another that talks about tables specifically all of my information is in these two textbooks if you're more concerned about normalizing a database from scratch or fixing problems with an existing database that wasn't normalized correctly this concept of database management book is the thing for you if you already have a healthy relational database and you just need to apply access in more productive ways than this microsoft illustrated access 2019 textbook is the book for you now to show you how tables are related i'm going to use the database out of my illustrated microsoft access 2019 book has four tables companies contacts industries and jobs and in the previous screencast we talked about how to create tables and that is that you've got to go into design view and build your field names select your data types and work on field properties to continue to modify each and every field as desired very helpful if you think through these things before you start entering your data but there's one really important aspect of building tables that i didn't speak about comes super important when we're connecting our tables together and that is that it's a good practice to have each table have a primary key field a primary key field is a field that contains unique data per record you might think that company name would be a good primary key field but we could theoretically have two companies with the same name yet have them be different companies so in this case i've added a company id field with an auto number data type it simply automatically numbers each record similar to a check number and i've set that as my primary key field with the primary key field button and symbol if you look at that in data sheet view we can see our company ids 1 2 4 17 let's see let's go ahead and sort them in ascending order on company id and we see that we have all these different companies now in the beginning an auto number field we'll start with one and it will be the same number as the number of record invariably we're going to working with the records and deleting certain records and so in the jobs table we jump from one to three to four to five and if i go to the end of the data sheet i'm at phone number e8 because that job id field is an auto number field is also set as a primary key field sometimes people get upset the number of records and the auto number number get off because we've deleted records in our process of updating them but do not worry about that the main thing about an auto number primary key field is that that value is unique per record we really don't care what the value is we just care that it's unique it's like a check number we don't care what the check number is we just care that it's unique not reuse these auto numbers just like we do not reuse checked numbers we void the check that number's gone forever if we delete a record that number's gone forever so it helps us with our checks and balances and our audits as well before we connect these tables in one-to-many relationships the first step ought to be going through them and making sure that each one has a valid primary field jobs it's job id it's an auto number field for companies it's company id it's an auto number field for contacts okay there's no field set as the primary key field but i do have a field set up here contact id as an automatic number because it could be multiple first names that are the same multiple last names that are the same we're going to go ahead with the contact id field and set that as the primary key field in industries we only have one field and that's the industry name it's a short text data type and we want each of these industry names to be unique so the industry field itself was set as the primary key field once we have a good primary key field on each of the tables we're ready for step two which is to relate the tables access we relate our tables going to database tools relationship screen and i've already got two valid relationships set up let's just pull the contacts table in here as well i'm going to take this relationship here first and explain how these two tables are related in relational database we do not want to repeat any data that we don't have to however to relate to tables we do have to have one field that is common two tables in order to join them in a one mini relationship and that is the key phrase that you want to say to yourself over and over and over again one record in the company's table can be related or connected or linked to many records in the jobs table and why because one company may have several jobs open if you say it backwards can one job relate to many companies no one specific job is going to be for one company in most situations so when you try and say the one-to-many relationship backwards if you get it wrong it just kind of hurts your brain but if you get it right it just kind of makes sense it just feels good in your head one company can offer many jobs when you figure out which label is on the one side and which tables on the mini side you grab the primary key field of that table on the one side and you duplicate that field in the table on the mini side so for example company id number one might be related many jobs if company id one has five job openings then that company id one value is going to be listed here five times in five different records for five unique different jobs in the jobs table that's where the one and infinity symbols come from the one symbol always goes with the table on the one side and therefore always goes with the primary key field in the table on the one side the infinity symbol always goes with a non-primary key field that's called the foreign key field in the table on the mini side let's look at this relationship one industry can be related to many companies that's right one industry if i open up the industries table such as aerospace can be related to three different companies and that's why when you open up these sheets you see these expand symbols the left of records on the one side of a one-to-many relationship one industry can be related to many companies look see the expand buttons the left of the company records as well because one company be related many different jobs the company id 13 has six different jobs available and so as you're going through the data sheets if i just open up the company's table it's a little bit more obvious one company can have many different jobs aba solutions only has one job it's like an accent group it has three jobs available so these little plus signs expand buttons are trying to show you the relationships between one record in the table on the one side and there are many related records in the table on the mini side and that's actually accomplished by having a common field doesn't have to have the same field name but it usually is just for clarity one common field that connects those records so one industry can be related to many companies and one company can be related to many jobs let's talk about this context table because it's not currently related in this relational database we have to figure out how we're going to connect it so we have to understand its relationship to the rest of those tables so we just start saying one-to-many relationships to ourselves until one makes sense and describes the business case one company have many contacts in this database or does one specific job have many contacts well that could be based on your business and i could see an argument going either way but for this database we're going to have contacts related directly to companies one company can have many contacts so once we know the table that's on the one side we're always going to grab that primary key field and then we're going to relate it to the linking field in the mini table but look through these fields in the mini table contact id first name last name phone and email i don't have a linking field in the contacts table to connect to the company's table that's a very common problem i have to create it i'm going to right click build list go into table design add a new field company id i'm going to give it a number data type because it's going to hook up with that auto number that's created in the companies table i'm going to do a little description here you can remember why i added it it's going to be the quaranti field to the companies table and now i go back to this relationship and now my one company can be connected to many contacts now i have a field to connect them to do that connection i merely drag from one table to the next i usually drag from the one table to the mini table it just makes more logical sense to me and release and i get the edit relations dialog box it's telling me we're going to use this company id field and the companies in the contacts table to make the connection if i click create here i do a relationship look it's kind of a wimpy relationship line because it does not have that infinity symbol over here on the mini side get the infinity symbol on a relationship i'll double click this relationship when this enforced referential integrity pick box is checked so i'm going to go back to this relationship and i'm going to go ahead and check that in force referential integrity check box that's one to many relationship and click ok now one company can be related to many contacts and i have my one and infinity symbols clearly on the relationship line show which table clearly on the one side and which table is clearly on the mini side this enforce referential integrity check box is so important and lays down some rules on the relationship that are really going to help you maintain the accuracy and integrity of your data it's so important i'm going to do a separate screencast on that but for now i want you to know that the relationships between these tables are defined and created in the relationships window and when they're created in the relationships window they're already connected for you when you build queries forms and reports which adds an enormous productivity boost to the overall performance of your relational database i've seen a few databases out there in the real world where people haven't spent the time to relate their tables in proper one-to-many relationships at the relationships window and instead relate their tables at query level that will drag down the performance of your database by a significant amount you do not want to do that you want to set up your relationships in your relationships window preferably before you ever start doing data entry in these one to many relationships with referential integrity and force thank you for listening and stay tuned for the next screencast if you want to know more about referential integrity and how it improves the integrity of the data in your relational database thank you

Channel: Lisa Friedrichsen

Views: 551

Rating: 5 out of 5

Keywords: Microsoft Access, Access, tables, relationships, one-to-many, primary key field, foreign key field, Relationships window, Lisa Friedrichsen

Id: nvG__MIo0v4

Length: 11min 45sec (705 seconds)

Published: Wed Jul 01 2020