Linking Tables in Microsoft Access Queries

in this tutorial we're going to take a look at creating joins or links between tables and Microsoft Access inside a query in the example that I have up on my screen you can see that I have two tables I have a book project table and a book sales table and just to give you a little bit of an idea of the data that's in these two tables and why we would want to join the two my book project table lists information about books that are under development so these are book titles that the company is going to be releasing in the future or that it's released in the past you can see that each book has a part number and the part number is the primary key in this table and if you're not familiar with primary Keys primary key in a table is a unique value or a unique field a value that's unique for every record in the table and it's in essence the field that we can look up and use as an identify or to find a particular book rather than having to rely on the title itself right now that primary key is important because the primary key in a table is usually the field that we also use to link or join two other tables within the database and you can see in this example the books sales table has that same part number field well it's not indicated as a primary key in the book sales table that's actually considered what's called a foreign key meaning that it is used to join this table the book sales table to another table the book project table on the part number field so they have a piece of information in both tables that's the same and that's how access is able to join the two together and get records that match from both so in our query our goal is to list all the books that we've developed in the past and also find out what sales we had for those books and what customer bought the books and the part number is going to be a key in helping us to join the two tables together and get that information out of the out of the query now if I don't join the two tables and you can see right now there is no join or link between these two and I try to run this query we'll take a look at what we get as a result and you can see that I have got as a total number of Records here twenty-nine hundred records now I can tell you that in the book title table if I go back to the design view here I already know for a fact that there are only about a hundred and five a hundred and eight or so records in the book project table so the fact that we got twenty nine hundred records back tells us that there's something wrong now the reason we got twenty nine hundred records back is because Microsoft Access by default when we don't explicitly join the two tables together or link them uses what's called a Cartesian join in our key our Cartesian join is really no joint at all what it means is that it's going to take every single record in the book project table and it's going to match it up with every other record or every single record in the book sales table so it goes in and it finds the first book title in the book project table it uses that part number goes into the book sales table doesn't look for their matching part number over there because it doesn't know how these two tables are related instead what it does is it matches it with record number one and outputs that matches it with record number two outputs that and does it for every single record in the book sales table so when I run this again you'll see that our book titles are actually repeating so here's basic home wiring the part number is 31 79 C and we can see an order of one and the quantity sold of 15 here but if I go a little bit further down in my results I just see the same book again and I'll see these books repeated over and over again one for each record that it finds in the sales table so that's definitely not the result that we want but just know that that is what's called a Cartesian join typically although it is used in some instances it's typically not the result that you want so we said that we wanted to get back all of our books in the book project table and only matching sales in the book sales table so that's going to require that we join these two tables by the part number field so in order to do that I'm going to click on the part number field in the book project table hold down my mouse drag it over to the part number filled in book sales and drop it off and you can see the accesses created a join or link between the two tables and by default when we do that access creates what's called an inner join and an inner join just means that access is going to return records from the two tables where it finds a match on the part number and what I mean by that is that some things could be left out so for instance let's assume that we've got some books in the book project table that have not sold right so there is no matching records or there are no matching records in the book sales table with that part number well of course we won't get any records back from the book sales table because there are none we just said that but what you might not expect is that we also won't get back that book or that books record from the book project table because it has to find a match on both sides so it's going to leave that record out entirely so we're going to get an incomplete picture of our book sales if we leave it at the default of an inner join now I'm just going to go ahead and run this so you can see what I mean here you'll see that when I run it we have a total of 100 records here in our result set but again I know that there's about 105 108 records total in the book title table you know we could open it up and take a look at it just as kind of a litmus test here a check on the data that we're getting back from this query but we can tell that we're not getting everything back and again that's because there are some books that people have not bought and therefore access can't find a match between the two tables on those records and so it leaves the book out entirely so what can we do well what we can do is point to this join or link line and double click on it and that brings up this joint properties window the joint property window shows us the table on the left our book project table on the table on the right our book sales table and the field that we're joining the two on that's all correct here's the reason we're getting that inner join because the default is number one only clewd rows where the join fields from both those are equal but we have two other options all right so I'm going to switch to option two here which says include all records from the book project table in only those records from book sales where the join fields are equal now this is sometimes referred to as a left outer join it's definitely an outer join but sometimes referred to as a left outer join and I say that just because this table happens to be on the left hand side and it really just means we're going to get all of the records from the left side or the left table in this relationship meaning the book project table all of the records will be output no matter what even if access doesn't find a matching record in the book sales table so I'm going to go ahead and click on OK and run that to see what we get back and you'll see that we now get back 108 records so now we're getting back all of the records from the book book project table and you can see that where access can't find a match in the sales table it simply leaves the corresponding fields blank all right so I'm going to go back to design view and let's just check out that last option number 3 which says include all of the records from the book sales table and only those records from book project where the join fields are equal right and this is another outer join referred to as a right outer join it's going to give us all the records from the right side or the right table in this relationship in only matching records that it finds on the left side so where this would make a difference is if we had some book sales that had for instance an invalid part number or a part number that access could not find in the book project table right maybe some somebody entered the part number in incorrectly that record would still be returned to us and we'd still get a total picture of our sales we just wouldn't have a matching book to show for it which would tell us that there was some problem with possibly the data entry that was done so I'm going to click on OK go ahead and run that and what we're going to see is that we get back to our original 100 records now what that tells us is that there are no unmatched records in the book sales table every every sales record has a valid matching book and as I scroll through these results you'll see that there are no book titles that are missing here that would be our clue that access couldn't find a matching book title for a sale right so we had three options there the inner join the default of number one and a left outer join or a right outer join and it really just depends on which table is the one that you want to see all of the records from in certain instances it might be the table on the left or it might be the table on the right but it's a good idea to come into the query editor and to try out these other two options to see how many records you get back and just do a check based on what you're seeing in the original table to determine whether you're getting back all of the records that you need or not you

Channel: SkillForge

Views: 104,320

Rating: 4.7181573 out of 5

Keywords: tutorial, training, Microsoft Access (Software), Software (Industry), Technology, Data

Id: byPxcW1I05c

Length: 9min 56sec (596 seconds)

Published: Wed Sep 11 2013