Table Relationships in Microsoft Access - joining tables in Access tutorial 2007 2010 2013 2016

in this video we're going to talk about uh table relationships in uh Microsoft Access uh now here we have a a simple database and this is actually some of the tables that come with uh The North Wind access database when you buy Microsoft Access uh it comes with it notice how we have the customers table and then we have uh the orders table well of course we can make multiple table queries and I have another video about that but um we can do the relationship at the query level or even at the report level or at the table at the form level however when you do it at the database level it'll carry through the entire database so you only have to set it up one time and then if you ever had a query that used both of those tables the query would remember what you set up on this screen so I i' like to do it at this level and there's some additional Cho choices that you need to know about as well so in this case I'm going to pick on database tools and then I'm going to pick on uh where it says relationships now when you go into the screen on your database you might see boxes and lines all over the place but this is how they were created or this is how you're going to create them in this case I'm going to pick on show table and that'll show a list of all the tables in the database you can pick as many as you wanted to here in this case I'm going to pick on the orders database actually pick on customers and I'll pick on orders now you can keep on picking on as many as you wanted to like I said I'm going to pick on close and we see both of those tables there uh these on are called field lists uh now notice have there's a key next to the customer ID field and that's how there's a key next to the order ID field in this table that that's because those fields are the primary keys of those tables now when you're joining those tables together you're looking for a common field I'm going to pick up the now it doesn't have to have the same field name although it's always very helpful when it does have the same field name but they have to have the same data type now in this case we can see there is a common field that's called customer ID so I'll click on customer ID and I'm going to drag it to the customer ID over here in the order side and then when I lift my finger this new window will appear notice how um it's using the customer ID from the customer side and it's using the customer ID from the order side notice down here it says one to uh that means One customer can have more than one order the reason that it made it a one to many is because the customer ID happens to be on the customer's table uh it has happens to be the primary key um so that means you know One customer can only be in there one time a specific ID can only be in there one time but the customer ID is not the primary key on the orders table so I One customer can have many orders that's why it says one to many there now if the customer ID was the primary key on both tables then the relationship type would say one to one if the customer ID was not the primary key on either table then it would say indeterminate or some datab bases call that many to many by an AIS it would say indeterminate there if neither um if the customer's table or the uh orders table didn't have the customer ID field as the primary key so that's what relationship TY means here it's one to many because it is the primary key in the customer table but it's not the primary key on the ORD table so one customer can have many orders uh now look at these choices over here these are optional let me explain what they mean uh I'm going to pick on uh enforce referential Integrity now that means it would be okay to have a customer in the customer's table that doesn't have a corresponding order but that would not allow you to have an order that does not have a matching uh record in the customer's table okay so let's say the customer ID is 123 it would be okay if 123 was on this table but not over here but when we check this box right here it'll prevent us from having customer ID 1 2 3 here that doesn't have a matching record over here now when you pick on that Top Choice the next choice lights up as well Cascade update related uh Fields what that means is let's say the customer number is 123 and they have some related records over here well if I change it to 234 over here it'll change the car sponding records over here so the change will Cascade through the coresponding records in this table now here it says Cascade delete related records that means if I delete customer 123 from the customer's table it'll also delete their corresponding record in the orders table usually when you pick one of those you pick all three of those okay so they all kind of go together these are just options I'm just kind of explain what the options mean I'm going to click on create now notice that how it actually shows you the one to many one to many uh uh customer to orders I'm going to double click on that line and that'll bring us back to that screen again there it uh now you you can see the different choices here here it says one to many now also I'm going to pick on something that's called the join type uh now the top one is what we call an inner join that means it's only going to show the records that have a match on both sides so I would have to have a corresponding customer uh that's also in the orders table the second one would show all of the customers even if they don't have a matching order record that's called the left Adder joint uh so let's say there was a brand new customer 345 in the customer's table but they did not have a matching order in the orders table that record would not show up for the first one because it it's not on both sides but that new record would show up on this one because that's going to show all of the customers even if they don't have a matching order it's called a left Adder join and then over here this would be a right Adder join that would show all the orders that don't have any even if they don't have a matching customer now in this case there wouldn't be any orders that don't have a matching Customer because we said enforce referential Integrity so actually that one would would not even apply in this situation although sometimes it does um but anyway that's what the join types mean I going to click on okay and the way I got to that screen was join type so I'm going to click on okay again and then we have the relationship between the tables uh I'm going to close that window and we'll save the relationships now let's make a query out of those two tables I'm going to click on Create and then query design I going to use the customer table and I'll use the orders table notice how um it remembers that relationship that we set up at the database level so that's why I like to go and do it in the database level because that way it'll be remembered uh any time we use those two tables in a in a a query or a form or a report uh so that should give you some good information about your table relationships here in Microsoft Access