Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database with 5905 rows of data that I want to update from another
database of 214 rows of data. One row is model number and the another is a
price. The problem is that some of the model numbers are the same and some
are not from the smallet database as the larger one. The only difference is
the price on the model numbers that are the same which needs to be changed.
There are also some new model numbers to be added with the price. So I need
to be able to update the larger one from the smaller one without duplicating
the model number but uppdate the price and add any new model numbers with the
price..
 
So, you have 2 steps to perform: an update, and an append.

1. Link the table from your other database:
File | Get External | Link
If the tables have the same name, Access adds a 1 suffix.

2. Create a query containing both the local table and the linked table.
Presumably the ModelNumber field is primary key in both.
If Access does not join the tables for you, drag ModelNumber from one table
onto the other.
Change the query to an Update query (Update on Query menu.)
Drag the Price field from the table you want to update into the grid.
In the update row, enter the price field from the other table, e.g.:
MyTable1.Price
Run the query.

3. Create another query. In the first dialog, choose the Unmatched query
wizard. This will select all the records from the linked table that are not
in the local table. Change it to an Append query (Append on Query menu.) Run
the append.

(If the tables do not have the matching primary key, the query at step 2 may
not be updatable.)
 
You need two queries.
An Update query to update the prices where the model numbers are the same.
An Append query to append records where the model number is not in the
target table.
 
Back
Top