1

I have two tables in MS Access. They are related (to the same Employee, common key = EmployeeID) but although they have many of the same data elements they must be stored and maintained separately. One table that is already populated (Employees) that contains the values I need, and another table with the blank fields (W-4). I need to populate the table with the blank fields (W-4) with the data from the elements of the same name from the table already populated (Employees).

Based on the above, I read from another thread that I can use an UPDATE statement, but use an inner join to get the data I need from table B to fill in table A:

UPDATE [Employees] INNER JOIN [W-4] ON [Employees].ID = [W-4].EmployeeID 
SET [W-4].field1 = [Employees].field1, etc etc etc
WHERE [Employees].EmployeeID = Form!ID;

I tried this in the MS Access Query (QBE) tool, and had mixed success: 1. It works if there's a record with the shared key in the target table 2. It doesn't work if there's no record at all for the shared key.

I read elsewhere on a different thread that, since I do not have (in most cases) any records in the target table with the shared key, that I would first have to do an INSERT, to get data populated initially, not an UPDATE.

So the reality is, I've got to do both - an INSERT when there's no data, or an UPDATE if there is data in the target table.

Scott Rutledge
  • 11
  • 1
  • As far as I know this is correct. In more fully featured database systems you can use the `MERGE` statement to handle this "insert if not matched, else update" type of logic, but I don't believe MS Access implements `MERGE`. Bear in mind `UPDATE` does exactly that, it updates rows that match the criteria. If there is no match, then there's nothing to update. Normally in this scenario you'd `UPDATE` matching rows first, then `INSERT` those rows without matches (otherwise you just end up double handling the 'missing' rows since you'd update them immediately after inserting). – Ben Jun 02 '19 at 23:03
  • Why not delete the rows in the second table and just insert the new values from the first? – Gordon Linoff Jun 03 '19 at 00:28
  • Thank you June7 for brining the Upsetting thread to light; I've checked it out and it has a lot of useful and helpful detailed instructions on constructing the SQL in VBA for this kind of UPDATE... – Scott Rutledge Jun 03 '19 at 02:39
  • Gordon Linoff - Thanks for responding. Can't delete all rows in 2nd table and just insert the new values, I need to 1), keep the historical values that were entered into the Form W-4 by/for the Employee over time (names, addresses, etc) and also 2), there are often differences in names used on employment records vs. W-4s that are submitted for tax purposes. So this is a more specific per-employee transaction that needs to happen "right now" for the next W-4 to be submitted (if there are changes)... Thanks! – Scott Rutledge Jun 03 '19 at 02:42

0 Answers0