How to Join Related Database Tables in an Excel Spreadsheet

• Microsoft Excel for Windows, versions 3.0, 4.0
• Microsoft Excel for OS/2, version 3.0

SUMMARY

The QE.XLA add-in macro supplied with Microsoft Excel will enable you to extract information from multiple external database tables.

This article explains the rules for using combined criteria, as explained in the "Microsoft Excel User's Guide," with the rules for joining tables, as explained in the "Q+E for Microsoft Excel User's Guide."

MORE INFORMATION

For example, you might want to extract information from the Employee database on all managers listed in the Department database. These managers have an ID number that is identical in both databases. We can use this ID to establish a relationship between these two database tables.

(See EMP.DBF and DEPT.DBF. These files are used in many examples throughout the "Q+E for Microsoft Excel User's Guide.")

In addition, we could add criteria to find only the records for all managers who are located in L31 or L04, have a salary of $48,000 or more, and were hired after January 1, 1985.

Four examples show how these things can be done:

• Example 1 shows how to use criteria to join two database tables.
• Example 2 shows how to set criteria using the AND relation.
• Example 3 shows how to set criteria using the OR relation.
• Example 4 combines examples 1, 2, and 3.

Setting Up the Worksheet to Use with the Examples

To demonstrate joining tables and setting criteria, a worksheet needs to be created that has proper external database definitions and a row of pasted field names. This can be accomplished by following these steps:

1. Load QE.XLA.

2. On a blank worksheet, choose Set Database from the Data menu and check the External Database check box.

3. Specify DEPT.DBF and EMP.DBF as your sources.

4. From the Data menu, choose Paste Fieldnames and choose the Paste All button.

5. With the field names still highlighted, choose Set Extract from the Data menu.

Example 1: How to Join Two Database Tables

EMP.DBF and DEPT.DBF both contain employee data. Each file uses a different field to hold the employee data. In EMP.DBF, this field is called EMP_ID; in DEPT.DBF, this field is called MGR_ID. To join information from these two tables, you MUST specify this relationship.

Cells N1 and N2 should contain the following:

   __|______N______|
   1 | dept.MGR_ID |
   2 | emp.EMP_ID  |

For every manager ID number in DEPT.DBF, find the corresponding ID number and record in EMP.DBF.

Extracting the Joined Records

1. Highlight N1:N2 and choose Set Criteria from the Data menu.

2. From the Data menu, choose Extract.

3. After Microsoft Excel reports that four records are found, choose Paste from the Edit menu.

To extract joined records using the AND relation, any number of additional criteria can be specified in additional columns on the same row:

     |______N______|______O______|______P_____|_______Q_______|
   1 | dept.MGR_ID | dept.LOC_ID | emp.SALARY | emp.HIRE_DATE |
   2 | emp.EMP_ID  | L31         | >48000     | >31048        |

   For all managers whose ID numbers occur in DEPT.DBF, find the
   corresponding ID number and record it in EMP.DBF for all managers
   who are located in L31 AND have a salary of $48,000 or more AND
   were hired after January 1, 1985.

1. Delete all previously extracted data.

2. Create the criteria as shown above.

3. Select cells N1:Q2 and choose Set Criteria from the Data menu.

4. From the Data menu, choose Extract.

5. After Microsoft Excel reports that one record is found, choose Paste from the Edit menu.

Example 3: How to Set Criteria Using the OR Relation

To extract joined records using the OR relation, any number of additional criteria can be specified in additional rows:

     |______N______|______O______|
   1 | dept.MGR_ID | dept.LOC_ID |
   2 | emp.EMP_ID  | L31         |
   3 | emp.EMP_ID  | L04         |

   For all managers whose ID numbers occur in DEPT.DBF, find the
   corresponding ID number and record it in EMP.DBF for all managers
   who are located in L31 OR in L04.

1. Delete all previously extracted data.

2. Create the criteria as shown above.

3. Select cells N1:O2 and choose Set Criteria from the Data menu.

4. From the Data menu, choose Extract.

5. After Microsoft Excel reports that three records are found, choose Paste from the Edit menu.

Example 4: Combining Criteria Using Both AND and OR Relations

To extract joined records using both the AND and the OR relation, any number of additional criteria can be specified in additional rows and columns.

Note that cells P2 and Q2 contain criteria as well, even though they did not in the previous two examples. The reason is that the criteria for emp.SALARY and emp.HIRE_DATE have to apply to ALL records that satisfy the dept.LOC_ID criteria, not just the ones for which dept.LOC_ID equals L31.

     |______N______|______O______|______P_____|_______Q_______|
   1 | dept.MGR_ID | dept.LOC_ID | emp.SALARY | emp.HIRE_DATE |
   2 | emp.EMP_ID  | L31         | >48000     | >31048        |
   3 | emp.EMP_ID  | L04         | >48000     | >31048        |

1. Delete all previously extracted data.

2. Create the criteria as shown above.

3. Select cells N1:Q3 and choose Set Criteria from the Data menu.

4. From the Data menu, choose Extract.

5. After Microsoft Excel reports that three records are found, choose Paste from the Edit menu.

REFERENCES

"Q+E for Microsoft Excel User's Guide," pages 15-18, 56-66

"Microsoft Excel User's Guide," pages 371-377