Updating and Moving Records

The data you keep in tables is rarely static, and over time, it is often necessary to change or move records. Sometimes it is necessary to change a value in multiple records in a table. You could update each record in turn , but an easier way is to use an update query. You might also find that as information changes, it s easier to keep current data in one table and archive old data in another. Tip Top Roofing s Jobs table is a good example. If you keep all the jobs the company has ever worked on in one table, the table will soon become too large to use on a daily basis. The solution is to have a separate table for the records of jobs that are complete. In this topic, we ll show you how to manage changing data.

Updating Records by Using a Query

Suppose the post office has assigned a new ZIP Code to Pinewood, and you need to update the Employees table to reflect the change. Follow these steps:

1. Select Employees in the Tables list of the Database window, click Query in the New Object button s drop-down list, and then click OK .

   Access opens a new query based on the Employees table in Design view.

2. In the Employees box, double-click the PostalCode field to add it to the QBE grid.

3. On the toolbar, click the Query Type button s down arrow, and then click Update Query in the drop-down list.

   Access changes the select query to an update query and adds a row titled Update To to the QBE grid.

4. Click the Update To row of the PostalCode column, and type 98415 .

5. In the Criteria row, type 98412 , and press Enter .

   In an update query, if you enter information in the Update To row but leave the Criteria row blank, Access assumes that you want to make the change no matter what the existing field value is. The result: Access updates every record in the table. So be careful when you run update queries.

6. Run the query.

   Access advises you of the number of records to be updated.

7. Click Yes to complete the changes, and then close the query without saving it.

8. Open the Employees table.

   All the records for customers who live in Pinedale now have the new ZIP Code.

   For a small table, it might be much faster to make the changes manually. For large tables, using an update query is definitely faster and ensures that all the affected records are changed.

Moving Records by Using a Query

To set the stage for the next example, you need to change the Status field values of two records in the Jobs table to Closed , indicating not only that the work is complete but that payment has been received. You also need to create a new table to contain the records for closed jobs. Let s get going:

1. To keep your desktop neat, close all windows except the Database window without saving anything. Then open the Jobs table.

2. Change the Status values for the records with job numbers 018022A and 0182730 to Closed .

3. Enter 2/1/03 in the Date Completed field for job number 018022A and 3/1/03 as the date of completion for job number 0182730 . Then close the table.

4. With the Jobs table selected in the Database window, click the Copy button on the Standard toolbar.

5. Click the Paste button on the toolbar to insert a copy of the table.

   Access displays the dialog box shown in this graphic:

   

6. In the Table Name text box, type Closed Jobs . Then select the Structure Only option, and click OK .

   Access adds the new table to the list in the Database window.

7. Open the Closed Jobs table in Design view, and delete the Status field by clicking its row selector and pressing Delete .

8. Close the table, and save your changes when prompted to do so.

   You can now move the closed records from the Jobs table to the Closed Jobs table. This is a two-step, append-anddelete operation.

9. Click the Jobs table in the Tables list of the Database window, click Query in the New Object button s drop-down list, and click OK to open the query in Design view.

10. Add all the fields from the Jobs box to the QBE grid by double-clicking the Jobs title bar, pointing to the selection, and dragging the image of the fields to the first column of the grid.

11. In the Criteria row of the Status field, type Closed .

12. Click Append Query in the Query Type button s dropdown list.

    Access displays the dialog box shown in this graphic:

    

13. Click the Table Name text box s down arrow, click Closed Jobs as the name of the table to which you want to add the results of the query, and click OK .

14. Run the query.

    Access tells you that it will append two rows, as shown in this graphic:

    

15. Click Yes to proceed with the query, and then close it, saving it as Append Closed Jobs .

    By saving the query, you can run it whenever you need to append records from the Jobs table to the Closed Jobs table.

16. Open the Closed Jobs table to verify that it contains the two closed records.

    The table appears as shown in this graphic:

    

    You have copied the closed records to the Closed Jobs table, but they still exist in the Jobs table. You don t want closed jobs included in the table of active jobs, so you need to delete the records from the Jobs table.

17. Close the Closed Jobs table, and open the Jobs table.

18. Click the record selector for job 018022A , press Delete , and click Yes to delete the record.

19. Repeat step 18 for job 0182730 , and then close the Table window.