4. MICROSOFT ACCESS QUERIES
4.7: Joins
If a query must be answered using data that appears in more than one table then the query requires a database join.
Suppose we wish to produce a list of member names and the call numbers of books they have borrowed. Important points about this query:
-
-
- The Loan table has the loan information we need.
- The Member table has the member names we need.
-
Before we compose the query consider how you would produce the results if you were to do this manually. If you had two listings showing the rows of each table in front of you on your desk you could proceed as follows going through the Loan table listing row by row starting with the first row:
1) For the current row in Loan:
-
-
- Write down the call number of this loan.
- Let N stand for the value of the member`s id for this loan.
- Now look at the Member listing row by row starting with the first row: Examine the row to determine if the row is for member N, and if it is, write down the member`s name beside the call number.
-
2) If there are more rows in Loan, advance to the next row and go back to step 1
In the above algorithm we have determined a member id at step 1.b) and we next look for a matching member id in step 1.c). For a human the process is simple but tedious. We could say we are trying to go from a row in Loan to a row in Member based on rows having the same value for member id. In database terminology we say we are joining Loan to Member based on a common value of member id. A tedious but well-defined task is something a computer can excel at, and fortunately we can get the database system to do the job of joining rows, based on values of a common attribute, for us. Construct the query in the following way:
-
-
- Create a new query
- Drag the Member and Loan tables into the Relationships Area:
-
-
-
- Note the line connecting the two tables. This is called a relationships line which causes MS Access to join pairs of rows – a row in Member is joined to a row in Loan where the two rows have the same value for id.
- Select the call number, first name, and last name fields by double-clicking them to obtain:
-
-
-
- Run the query and you see the results:
-
Exercises
In each of the following exercises the necessary data is in more than one table and so it is necessary to specify a join.
1) For each loan show the title of the book and the date it was borrowed. Note that the title is in the Book table and the date borrowed is in the Loan table.
2) Modify the previous query to produce a listing that is in order by title and then by date.
3) Produce a list that shows for each loan the book title, the name of the member who borrowed the book, and the dates the book was borrowed and then returned. Note that 3 tables are needed for this query:
-
- Book joins to Loan
- Loan joins to Member
4) Produce a list of members and the books they have taken out on loan. Include the member’s last name, first name, and titles of the books. The information to be displayed is in 2 tables, but it is necessary to specify 3 tables for this query:
-
- Member joins to Loan
- Book joins to loan
5) Modify the previous query to produce a listing that is in order by last name and then by first name.
6) For member id 2, list the person`s name and the titles borrowed.
7) Produce a list of book titles and member names for those books that are due back May 18, 2014.
8) Produce a list of book titles and member names for those books that have not been returned. In this case you must give the criteria for dateReturned as null. Null is a special keyword that represents no value.
