You are currently offline, waiting for your internet to reconnect

ACC2000: How to Update and Append Records in One Update Query

This article was previously published under Q208870
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

In Microsoft Access, you can use an update query to append unique newrecords to a table, as well as update existing records in the table. Forexample, by using two tables (Table1 and Table2) in an update query, you can update older records in Table1 by using newer data from Table2, and you can append any unique new records to Table1 from Table2.

NOTE: An update query can append and update records only if the tables contain a unique index that does not have the AutoNumber data type.
To update and append records in one update query, follow these steps:

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Start Microsoft Access, and then open the sample database Northwind.mdb.
  2. Click Shippers in the Database window, and then on the Edit menu, click Copy.
  3. On the Edit menu, click Paste. In the Paste Table As dialog box, type Shippers1 in the Table Name box, and then click OK.
  4. Repeat Step 3, typing Shippers2 in the Table Name box.
  5. Open the Shippers1 table in Design view, change the ShipperID field as follows, and then save and close the table:
    Field Name: ShipperID
    Data Type: Number
    FieldSize: Long Integer
  6. Repeat Step 5 for the Shippers2 table.
  7. Open the Shippers2 table in Datasheet view, and then modify the first record to:
    Shipper ID: 1
    Company Name: Ultimate Speedy Express, Inc.
  8. Add the following new record, and then save and close the Shippers2 table:
    Shipper ID: 4
    Company Name: Super-Fast Delivery
  9. Create a new select query in Design view based on the Shippers1 and Shippers2 tables.
  10. Double-click the join line between Shippers1 and Shippers2 to openthe Join Properties dialog box. (If not created automatically, join the tables by using the ShipperID field.) Select the following join type,and then click OK:
    Include ALL records from 'Shippers2' and only those records from'Shippers1' where the joined fields are equal.
  11. On the Query menu, click Update Query to change the select query to an update query.
  12. From the Shippers1 field list, drag the ShipperID, CompanyName, and Phone fields to the query grid, and then modify the Update To row as follows:
       Field: ShipperID      Table: Shippers1      Update To: [Shippers2].[ShipperID]   Field: CompanyName      Table: Shippers1      Update To: [Shippers2].[CompanyName]   Field: Phone      Table: Shippers1      Update To: [Shippers2].[Phone]					
  13. On the Query menu, click Run. When you are prompted to confirm the updates, click Yes.
  14. Open the Shippers1 table in Datasheet view. Note that it contains the modified record and the new record from the Shippers2 table.
For more information about update queries, click Microsoft Access Help on the Help menu, type troubleshoot update queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Article ID: 208870 - Last Review: 06/24/2004 18:11:00 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kbhowto kbinfo KB208870