This month, Doug Steele looks at how to update and insert in one step.
If I’ve got new data, how can I process it so that matching rows in my master table are updated, and new rows are inserted?
I can remember having to write complicated “balance line” programs to solve this with code in the past. Fortunately, it’s possible to do it in Access using a single query without having to write a line of VBA code!
The trick to accomplishing this is to remember that queries can join tables together. The most common join is an Inner Join, which combines records from two tables wherever there are matching values in the fields used to join the tables. An Outer Join also combines records from two tables, but an Outer Join includes all of the records from one of the two tables, even if there are no matching values in the other table. That sounds useful in your situation, doesn’t it? You have a set of new values and a set of existing values, and you want to combine the values, whether every one of the new values corresponds to an existing value or not.
Let me walk you through how to build this query in the Query Builder. For the sake of discussion, I’ll assume that there are two tables (ExistingData and NewData), and that the two tables are identical in structure.
- Create a new query.
- Add tables NewData and ExistingData to the query.
- If a relationship line isn’t drawn between the two tables, create the relationship now, relating the tables by their primary key. (Note that if it’s a compound primary key, you must join each of the fields in the index.)
- Double-click on the relationship line joining the two tables, and choose the option “Include ALL records from ‘NewData’ and only those records from ‘ExistingData’ where the joined fields are equal” and then click OK. (If you’re dealing with a compound primary key, you must do this for each line joining the tables.)
- Drag all of the fields from table ExistingData into the query grid.
- From the Query menu, select Update Query to change the Select query to an Update query.
- For every field in the query, go to the Update To cell and type [NewData].[<name of the field>]. (Don’t type the pointy brackets—”<” and “>”—that I show around “name of the field.”) Yes, this is time-consuming, and there’s no automated way to do it, but fortunately you only have to do it once!
- Save the query with an appropriate name.
That’s it; you now have a query that will update or insert records as required. If you look at the SQL for this query, you should have something like this:
UPDATE NewData LEFT JOIN ExistingData
ON NewData.ID = ExistingData.ID
SET ExistingData.ID = [NewData].[ID],
ExistingData.Field1 = [NewData].[Field1],
ExistingData.Field2 = [NewData].[Field2],
ExistingData.Field3 = [NewData].[Field3]
and so on.
When you run the query, the query brings back one row for each row in the NewData table, whether or not a corresponding row exists in the ExistingData table. (The values will be Null for each field in the ExistingData table when the value exists in NewData but not in ExistingData.). Each row is updated to include the values from the corresponding row in the NewData table. You’ll be updating either existing values (when the corresponding row does exist in ExistingData) or Nulls (when the corresponding row doesn’t exist in ExistingData).
Hopefully, it’s obvious that this will only work if you have a primary key defined for each table. It doesn’t matter whether that primary key is a single field or a set of fields, as long as it’s the same field(s) in both tables.
Your download file is called Steele_Two_Things_AA200311.accdb