| You direct the facilities department at Ballou Realty. Ballou leases space in its several buildings to other companies in need of office space. Your staff has built several databases to help maintain information on the offices themselves, on building equipment such as air conditioners and PBXs, and on building components such as windows and doors. One of your databases contains records that describe the doors in Ballou's office buildings: doors to offices, doors to closets, doors that separate halls, doors to the outside, and so on. For various reasons, including repair and warranty issues, you find that it's necessary to know the name of the maintenance technician who last inspected each door. One way to set things up is to have a field, perhaps named TechName, in your Doors table. Then when a Ballou technician inspects a door, one of the items that's recorded is the technician's name. But you want to be careful not to have to type the technician's name into each record. If you do that, a simple misspelling creates a new technician: "Smith" might have done the work, but if someone types "Smit" instead, any data summary involving the technicians will be wrong. It will show that a door has been inspected not by Smith, but by the nonexistent Smit. Before you took your job with Ballou, the Facilities staff maintained data using Excel alone. They prevented the misspelling problem by setting up a validation list, by choosing Data, Validation and allowing a list (see Figure 5.2). Figure 5.2. The validation list is yet another good opportunity to use a dynamic range name. 
You have implemented a departmental policy to store information on facilities in a true database. The avoidance of keying errors by using Excel data validation has become irrelevant. To help ensure data integrity in a database, you create a table with (in this example) the names of the technicians who work for Ballou, and arrange for your main Doors table to display the available technician names in a dropdown. Selecting a name from that dropdown avoids typing errors. You name the table Technicians, and store the technicians' names in a field named TechName. It's useful for that Technicians table to have a field with a unique record ID, perhaps TechID, and for your Doors table to have a field with the same name. Figure 5.3 shows how this might be set up in Microsoft Access. Figure 5.3. Each table has a TechID field. This paves the way for a link between the two tables. 
The Technicians table might have a record whose value for the field TechID is 1, and the value of TechName is Fred Tafoya. If Fred Tafoya was the last Ballou technician to inspect a particular door, that door's record would have the value 1 in the TechID field in the Doors table. Given that setup, you return records to Excel via Microsoft Query by taking these steps: - With an Excel worksheet active, choose Data, Import External Data.
- Create a New Data Source or use an existing source. The Microsoft Query window appears, along with the Add Tables box.
 Creating a new data source is described in "Getting External Data into the Workbook," p. 85.
- Click the Doors table in the Table list box to select it. Click the Add button to put the Doors table in the table pane.
- Repeat step 3 for the Technicians table.
- Click the Close button to dismiss the Add Tables box.
The table pane now appears as in Figure 5.4. Figure 5.4. To return all the table's fields, double-click the asterisk at the top of a field list or just drag it to the data pane. 
A line, termed a join line, appears between the two tables, connecting the TechID field in the Doors table to the TechID field in the Technicians table. NOTE Under some circumstances, the join line does not appear automatically. If it doesn't, just click one table's TechID field, drag to the other table's TechID field, and release the mouse button.
To return the ID of the door and the name of the technician who last inspected it, drag the DoorID field from the Doors table into the data pane. Then drag the TechName field from the Technicians table into the data pane. The result is shown in Figure 5.5. Figure 5.5. You don't need to move either TechID field to the data pane in order to return the TechName field. 
When you choose File, Return Data to Microsoft Excel, the Import Data dialog box appears. Click OK to accept its placement of the records on the worksheet as shown in Figure 5.6. You can now use Excel to review information about the ongoing maintenance of the doors in Ballou's office buildings using Excel's data analysis tools, such as pivot tables and charts. Figure 5.6. The results of your query: records from two fields in different tables, joined by an unseen common field. 
| 