| You're responsible for a database that maintains records on a hospital's employees. One of the requirements for the hospital to maintain its accreditation is that all employees pass an annual examination on safety procedures: what to do if an infant is abducted, where to find material safety data sheets, how to report a hazmat spill, and so on. The test is administered online using the hospital's data network, and one of the database's tables is keyed on the employee's unique ID number. It also stores the date he took the test, his response to each of 30 test questions, and the number of questions he answered correctly. The hospital's human resources department maintains another table that contains other information such as the employee's Social Security number, the department he works for, his hire date, and all the other personal information needed on an employee of a highly regulated industry. The HR table is also keyed on the employee's unique ID. From time to time, the hospital wants to analyze two critical summary variables: the average percent correct on the safety test for each department, and the percent of current employees who have taken the test, again by department. A department with an unusually low mean score on the test receives additional training. A department that is lagging in completion rate is targeted for unwelcome attention from the hospital's administration. You use a Select query to join the Safety Test table to the Employees table, and use that query (see Figure 9.15) as the data source for an external data range on an Excel worksheet (see Figure 9.16). Figure 9.15. The query returns for each department a count of employees and of test completions to calculate a percent. 
Figure 9.16. An analysis of percents should usually include the percent's denominator (here, the employee count per department). 
TIP To get the query shown in Figure 9.15 to display the PercentComplete field in percent format, first click any row in the column in Query Design view to select a cell in the design grid. Then right-click the cell (you can't start by right-clicking in the design grid; Access assumes you're right-clicking the query, not the cell). Choose Properties from the shortcut menu and in the Field Properties window choose Percent for the field's format. This controls the display of the field in Access only if you want to show the field as a percent in Excel, you'll need to format it on the worksheet too.
One day you learn that HR will no longer maintain data on ex-employees in its Employees table, but will archive the data in a Terminations table. Once monthly, the records of employees who have left the hospital are removed from the Employees table and placed in the Terminations table. This causes you some minor heartburn. The hospital wants to see departmental results both for current employees only, and for all employees since it started administering the test. The query shown in Figure 9.15 relies on the Employees table to provide each employee's department, but if ex-employees are no longer to be kept in that table, you'll have to look elsewhere for department membership. There are various solutions, but you decide that the most straightforward is to begin storing the employee's department in the Safety Test table. Before the HR department archives records on ex-employees for the first time, you duplicate the Department field from the Employees table in the Safety Test table. Then you create the Update query shown in Figure 9.17. Figure 9.17. Note that an inner join is used: It returns only records where the joined fields from both tables are equal. 
To create the query shown in Figure 9.17, take these steps: - From the main Access window, click the Queries tab.
- Click the New button, choose Design View in the New Query dialog box, and click OK.
- Add the Employees table and the Safety Test table to the query's design pane and then click Close in the Show Table dialog box. If, as here, the tables' primary key fields have the same name, Access automatically creates a default join between the two tables. (If not, you would create the join yourself by clicking one field and dragging to the other.)
- Choose Query, Update Query. This changes the active query from the default Select query type to an Update query type. Notice that the Sort row and the Show row are removed and replaced by an Update To row.
- Drag the Department field from the Safety Test table to the query design grid's first column. Or, click in the Field row and select Safety Test.Department from the dropdown list.
- In the Update To row under Department, enter [Employees].[Department]. Because there are two Department fields in the query one from each table you need to qualify the one that you want to update from.
- Choose Query, Run or click the Run button on the toolbar. Click Yes in response to Access's confirmation message.
NOTE The query uses an inner join: It returns only those records that exist in both tables, and therefore does not try to update a record found in the Safety Test table that is not found in the Employees table. It could also use an outer join that returns all records from the Safety Test table and only those records from the Employees table with a matching value on EmpID. Suppose that instead you use an outer join that returns all records from the Employees table and only those records from the Safety Test table with a matching value on EmpID. Then the update query will attempt to update nonexistent records in the Safety Test table. If you're running the query manually, you'll get a warning message that some records were not updated due to key violations. You can bypass the warning, but it's an annoyance you don't need. Unless you're sure of your ground, use inner joins in multi-table Update queries.
 For more information about inner and outer joins, see "Joining Parent and Child Records," p. 119.
Using Delete and Append Queries It's not too difficult to delete a record from a table in a database: just open the table, find the record, select it, and then press the Delete key. But when you have many records to delete, and when for one reason or another you need to delete the same records over and over, you don't want to do it manually. Apart from the time you waste, there's always the nagging little doubt that you might have deleted the wrong records. Delete queries can come in handy here. They're faster than deleting records manually, and you can rely on them to act the same way time after time just make sure that you set them up correctly to begin with. Chapter 4, "Importing Data: An Overview," mentioned the difficulty you encounter when an application stores data internally but does not offer you a straightforward way to access it. Fortunately, such applications generally provide the user with a report writing capability, which exports data in either a formatted report layout or a comma-separated values (CSV) format. In some cases you must access and reuse this data frequently. Then you'll want to use Delete queries in conjunction with Append queries. When you have this sort of situation, it usually helps to use VBA code to run the queries. It's a more effective use of your time to run a single VBA procedure that executes several queries than to execute each query in turn by hand. | 