ACC: Updating and Appending Records in One Update Query

MORE INFORMATION

1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. Select the Shippers table in the Database window, and 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 (or Shipper ID field in version 2.0) 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 modify the first record to be:
   

           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 open the Join Properties dialog box. (If not created automatically, join the tables on the ShipperID field.) Select the following join type, and then choose 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 (or Update in version 2.0 and 7.0) 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 (or the Shipper ID and Company Name fields in version 2.0), and then modify the Update To row as follows:
    
    NOTE: In Microsoft Access 2.0, there is a space in the Shipper ID and Company Name fields, and there is no Phone field.
    

            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 OK.
14. Open the Shippers1 table in Datasheet view. Note that it contains the modified record and the new record from the Shippers2 table.