Complex Table Updates (1 Viewer)

I have a 'Master' table held in an MS Access database which looks a bit like this:


I also have a monthly 'Update' report containing a complete list of up-to-date records. It looks a bit like this:


I want to add new records to my Master table from the 'Update' report using the following criteria:
Where the combination of a 'Human ID' and 'Company Name' is unique, append the record to the 'Master' table. This means ignoring any records where the 'Human ID' and 'Company Name' combination already exist on the 'Master' table.
In the example above, Human ID number '1' would not be added to the 'Master' as the combination of '1 - def' already exists.


Now here's where it gets complicated...
Once the update report records have been appended to the Master sheet, I need to identify any duplicate 'Human IDs'. This can occur when a human changes company (like in the case of Human ID '2'). '2 - abc' would have been added to the bottom of the Master from the first step (as it has a unique combination of 'Human ID' and 'Company Name'), but '2 - def' will also​remain to exist on the Master. Assuming the old record '2 - def' has no status assigned to it and no notes assigned to it, it can be safely removed from the Master table, preserving only the newest record from the update report. However, in the event the old record contains a note and/or status, it is important that both the old record and the new record are preserved for historical purposes.

Has anyone got any ideas as to how I can automate this update in access using a query of some kind?

I'm fairly new to Access so any advice would be greatly appreciated.


Many thanks!

You have conflicting demands here. You both want to avoid duplicates and sometimes keep duplicates. That's not a good way of working!
You could set a composite index for Human ID & Company Name to prevent duplicates but will need to remove existing dupes first. Suggest you add a yes/no field called Active with default value =true. Use an update query to deactivate all 'old' values by setting them false.
Once you have done that, you can append new 'unmatched' records and update existing records using separate queries (or possibly using one combined 'upend' query). But I think you need to clarify your requirements first.
Also recommend you avoid spaces in field names