Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Resources » Articles » Databases »
Searching Multiple Tables In Access
|
Searching Multiple Tables
At this point, you've been through all the variations on a single theme—queries on a single table. It's easy to build on this knowledge to retrieve related information from many tables and to place that information in a single view. You'll find this ability to select data from multiple tables very useful in designing forms and reports.
Try the following example, in which you combine information about an entertainment contract and about the club where the entertainment is to be performed. Start by bringing the Entertainment Scheduling Database window to the front. Click the Queries button, and then click the New button. Select Design View in the New Query dialog box, and click OK to open a new Query window in Design view. Access immediately opens the Show Table dialog box. In this dialog box, you select tables and queries with which to design a new query. Select the tblClubs and tblContracts tables, click the Add button, and then close the dialog box.
If you defined the relationships between your tables correctly, the upper part of the Query window in Design view should look like that shown in Attached Figure 8-36. Access links multiple tables in a query based on the relationship information you provide when you design each table. Access shows the links between tables as a line drawn from the primary key in one table to its matching field in the other table. If you don't define relationships between tables, Access makes a best guess by linking the primary key field(s) in one table to those that have the same name and matching data type in other tables.
Attached Figure 8-36. A query that selects information from the tblClubs and tblContracts tables.
In this example, you want to add to the query the ContractNo, GroupID, and ClubID fields from the tblContracts table and the ClubID, ClubName, and StreetAddress fields from the tblClubs table. When you run the query, you see the recordset shown in Attached Figure 8-37. The fields from the tblContracts table appear first, left to right. Notice the group name in the column for the GroupID field from the tblContracts table. If you check the definition of the GroupID field in the tblContracts table, you'll see a Lookup combo box defined—the query has inherited those properties. Click in a GroupID field in this query, and the combo box appears. If you choose a different group name in the drop-down list, you will update the GroupID for that contract. Likewise, the ClubID field from the tblContracts table displays the club name in a combo box, not the underlying ClubID values. You can scroll to the right to see the fields you added from the tblClubs table.
Attached Figure 8-37. The recordset of the query shown in Attached Figure 8-36. The group information in the drop-down list comes from the lookup properties defined in the tblContracts table.
As mentioned earlier, you can do many of the things with Query windows in Datasheet view that you can do with Table windows in Datasheet view. To see club information alongside group information, you can select the columns containing club data and move them next to the Group ID column. You can also select the columns containing group information and then choose the Freeze Columns command from the Format menu. This action will lock those fields on the left side of the datasheet. You can then scroll to the right to bring the club columns into view.
One interesting aspect of queries on multiple tables is that in many cases you can update the tables from any of the columns. See the section titled "Limitations on Using Select Queries to Update Data" later in this chapter for a discussion of when joined queries are not updateable. For example, you can change the club name in the tblClubs table by changing the data in this query's datasheet.
CAUTION
--------------------------------------------------------------------------------
Because the club name comes from a table on the "one" side of a one-to-many relationship (one club has many contracts, but each contract is for only one club), if you change the name of the club in any row in this query, you change the club name for all contracts for the same club.
Likewise, you can change the ClubID from the tblContracts table (the one on the left in this example) by choosing a different club name from the drop-down combo box, and Access will automatically update the new related club information. Try changing ClubID in the first row from "Alligator Club" to "CandyBox". (CandyBox is the first club in the drop-down list.) When you select the new value for the ClubID field in tblContracts, you should see the ClubID field from tblClubs change to 5000 and the Club Name entry change from The Alligator Club to CandyBox. Note that in this case you're changing only the linking ClubID field in tblContracts, not the name of the club in tblClubs.
Outer Joins
Most queries that you create to request information from multiple tables will show results on the basis of matching data in one or more tables. For example, the Query window in Datasheet view shown in Attached Figure 837 contains the names of clubs that have contracts in the tblContracts table—and it does not contain the names of clubs that don't have contracts. This type of query is called an equi-join query. This means that you'll see rows only where there are equal values in both tables. What if you want to display clubs that do not have any contracts in the database? You can get the information you need by creating an outer join. An outer join lets you see all rows from one of the tables even if there's no matching row in the related table. When no matching row exists, Access returns the special value Null in the columns from the related table.
To create an outer join, you must modify the join properties. Look at the Design view of the query you created in Attached Figure 8-36. Double-click the join line between the two tables in the upper part of the Query window in Design view to see the Join Properties dialog box, shown in Attached Figure 8-38.
Attached Figure 8-38. The Join Properties dialog box with the second option selected.
The default setting in the Join Properties dialog box is the first option—when the joined fields from both tables match. You can see that you have two additional options for this query: to see all clubs and any contracts that match, or to see all contracts and any clubs that match. If you entered your underlying data correctly, you shouldn't have contracts for nonexistent clubs. If you asked Access to enforce referential integrity (discussed in Chapter 5, "Building Your Database in Microsoft Access") when you defined the relationship between the tblClubs table and the tblContracts table, Access won't let you create any contracts for nonexistent clubs.
Select the second option in the dialog box. When the link between two tables involves more than one field in each table, you can click the New button to define the additional links. Click OK. You should now see an arrow on the join line pointing from the tblClubs field list to the tblContracts field list, indicating that you have asked for an outer join with all records from tblClubs regardless of match, as shown in Attached Figure 8-39. For clubs that have no contracts, Access returns the special Null value in the columns for tblContracts. You can see only the clubs that aren't generating any business by including the Is Null test for any of the columns from tblContracts. When you run this query on the data in the Entertainment Scheduling database, you should find exactly one club (the No One Wants to Work Here club, naturally) that has no contracts, as shown in Attached Figure 8-40. The finished query is saved as qryXmplClubsWithNoContracts in the Entertainment Scheduling database.
Attached Figure 8-39. You can double-click the join line between two tables in a query to open the Join Properties dialog box.
Attached Figure 8-40. The recordset that shows clubs that have no contracts.
Building a Query on a Query
You might have noticed in the Show Table dialog box in the query's Design view that you can select not only tables but also other queries to be the input source for a new query. In fact, another way to build queries using multiple tables is to use another query as input. To solve certain types of problems, you must first build one query to define a subset of data from your tables and then use that query as input to another query to get the final answer.
For example, suppose you want to find out which clubs or groups have no bookings in a certain time period. You might guess that an outer join using the tblContracts table will do the trick. That would work fine if the tblContracts table contained contracts only for the time period in question. Remember, to find clubs that aren't booked, you have to look for a special Null value in the columns from tblContracts. But to limit the data in tblContracts to a specific time period—let's say December 1999—you have to be able to test real values. In other words, you have a problem because a column from tblContracts can't be both Null and have a date value at the same time.
To solve this problem, you must first create a query that contains only the contracts for the month you want. As you'll see in a bit, you can then use that query with an outer join in another query to find out which clubs aren't booked in December 1999. Attached Figure 8-41 shows the query you need to start with. This example includes both the ClubID and the GroupID fields, so you can use it to search for either clubs or groups that aren't booked in the target month. A simple Between criterion in the BeginningDate field ensures that this query will return the correct rows. This query is saved as qryXmplBookDec1999 in the Entertainment Scheduling database.
Attached Figure 8-41. A query that lists contract data for a particular month.
After you save the first query, select it in the Database window and select Query from the New Object toolbar button's drop-down list to start a new query using the first one as input. In your new query, add tblClubs to the design grid by choosing Show Table from the Query menu and then selecting tblClubs in the Show Table dialog box. Access should automatically link tblClubs to the query on matching ClubID fields. Double-click the join line to open the Join Properties dialog box, and choose option 3 to see all rows from tblClubs and any matching rows from the query. The join line's arrow should point from tblClubs to the query, as shown in Attached Figure 8-42. (When you first add tblClubs to the query grid, it appears to the right of tblContracts. I repositioned the field lists with respect to each other. You can grab either field list by its title bar with your mouse to move it.)
Attached Figure 8-42. An outer join query searching for clubs not booked in December 1999.
As you did in the previous outer join example, include some fields from the tblClubs table and at least one field from the query that contains contracts only from December 1999. In the field from the query, add the special Is Null criterion. When you run this query (the results of which are shown in Attached Figure 8-43), you should find 16 clubs without bookings in December 1999—including the No One Wants to Work Here club that you found earlier. This query is saved as qryXmplClubsNotBookedDec1999 in the Entertainment Scheduling database.
Attached Figure 8-43. The clubs without any bookings in December 1999.
Using Multiple Tables in Total Queries
As you might suspect, you can also use multiple tables in a total query or in a crosstab query. Earlier in this chapter, you built a crosstab query to show monthly contract amounts for each group. (See Attached Figure 8-32.) You could do this using a single table as input because the application copies the relevant group name from the tblGroups table each time you create a new contract. In the form you use to create and edit contracts, some special Visual Basic code does this copying for you, which you'll see later in Chapter 23, "Automating Your Application with Visual Basic" However, if you want to see totals sorted by club name, you need to include tblClubs in this query so that you can use the related ClubName field to form the groups for the crosstab totals. (If you use the ClubID from tblContracts, you will see club names in the query output because a Combo Box is specified in the lookup properties of the table, but they will be sorted by the ID value, not by the name.)
Attached Figure 8-44 shows the crosstab query with the tblClubs table added. Instead of using GroupName from the tblContracts table for the row heading, you can now use the ClubName field from the tblClubs table. The settings for the field remain the same (Group By, Row Heading, and Ascending). Attached Figure 8-45 shows the recordset of the query, with club names instead of group names. This example is saved as qryXmplCrossTabClubs in the Entertainment Scheduling database.
Attached Figure 8-44. A crosstab query that uses multiple tables.
Attached Figure 8-45. The recordset of the crosstab query shown in Attached Figure 8-44.
 Attachments
Figures Understanding (34545-3146-Searching-Multiple-Tables-Figues.zip)
|
Responses
|
| Author: greeny_1984 03 Nov 2009 | Member Level: Diamond Points : 0 | Hi,
This article could have been more effective
with figures in them.
|
|