djmixer135
New member
- Local time
- Today, 09:40
- Joined
- Apr 15, 2019
- Messages
- 1
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 alsoremain 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!
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 alsoremain 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!