Putting Your Data to Work

Creating a Query Based on Two Tables with Related Data

Our simple query, shown in Figure 2, provides us with a helpful list of specific inventory, as well as crucial information about that inventory. From this query, I can see that one laptop in my inventory was purchased nearly three years ago and another was purchased two years ago. I can then make a note to check with the employees using these machines to see if any issues have arisen with this older equipment.

However, because the query was based on one table, it doesn't tell me the names of the employees who have these two machines. It lists only the employee ID numbers. I could open the Basic Employee Details table and search for the corresponding ID numbers. Or, I could create a new query that uses data from two related tables. The wizard doesn't allow me to do this, but Design View option does. Here's how:

1. Open the database.
2. Click the Queries icon in the left pane.
3. Choose Create Query in Design View. The Design View will open, with the Add Table or Query window displaying.
4. Because we want to include information from two tables in this query, we'll make two selections: select the Basic Employee Details table and click Add, then select the Inventory Details table and click Add. Close the Add Table or Query window.
5. You'll be able to see the relationship between both tables at the top of the screen. (See Figure 3.) But if the tables have not yet been joined, you can click the common field in one table (the Employee ID field in this case) and draw a line to the common field in the other table. The two tables will then be joined.
6. The next step is to add the fields you want the query to include. To do so, click the Field field in the first column and select the drop-down arrow. From the list that appears, choose the first field you'd like to display. Do this in each column until all desired fields have been chosen. (See Figure 4.) In our example, we used every field from the Inventory Details table except the Employee ID field. This field isn't needed because we used the fields for Last Name, Middle Initial and First Name from the Basic Employee Detail table. We also used the Department and Extension fields in the latter table.
7. Now, go back to each column and make designation for other fields as desired. In this example, we are not using aliases, but if you did want to change the way a field name was displayed, you would use the Alias field to enter the alias.
8. Click in the Sort field of the sixth column—the Date Acquired column—and select Ascending.
9. Make sure the box in the Visible field is checked for each column. If, however, there is a column you don't wish to display in the query but that is needed to perform a calculation, uncheck the box.
10. Click in the Criterion field of the second column—the Inventory Type column—and enter Laptop. Enter the term exactly as used in the original table. Press Tab to move from the field. The term will automatically be surrounded by quotes. (See Examples of Query Commands for more information on criterion possibilities.) Let's say you want to be even more specific and list only the laptops being used by the accounting department. If that's the case, enter Accounting in the Criterion field of the Department column.
11. Click the Run Query button. Your new query will display above the Design View editing area. As you can see in (See Figure 5), it now lists the laptops in use in the accounting department, as well as the names of the employees in the department who are using each laptop.
12. Make further edits as desired, clicking the Run Query button when finished to display the latest results.
13. When finalized, click the Save button, name the query, and click OK.
14. Close the query and save the database.

Design View can also be used to generate queries that require functions, such as counting or summing a detail. You might, for example, want to query the total dollar amount used to purchase inventory in each department. This could quickly tell your co-workers in accounting how much to budget per department for inventory needs in future fiscal years. To generate such a query, take these steps: