Find and replace changing records

[Nexus981]

Hi,

Find and replace changing records.

I get copies of a corporate directory each week. I want to upload the newest export and maintain a table of only unique records.

The Employee ID should be the unique record
When details change for the EE id I would like to keep those updates and get rid of the prior details
Secondly - I want to add new EE ID as they join the company
Third - I want to recognize when an EE ID is no longer on the newest export (left company) often times I get old data and without having a historical record I cant populate all the details I need or I have to search old files until I find a file with that EE ID within.

I think I could do this via excel but I really want to start using Access more .. Any help o get this started would be awesome.

Thanks for the help in advance!

 

[Joe4]

Welcome to the Board!

One way of doing this would be to import the weekly data file into a Temporary table, and then perform a series of queries between this Temp table and your Final table for your various tasks.

1. You can do a simple matching query between the two tables to identify records with the same IF in both. You can then check for differences, or perhaps just create an Update Query to update all the fields in your Final database with the values from the Temporary one.

2. You can do an Unmatched Query (there is a built-in query for this) from the Temp table to the Final table to find the new employees, and them add them using an Append Query.

3. You can do an Unmatched Query from the Final table to the Temp table to identify anyone who is not on the latest file.

I am not sure what you intend to do with the third group. If it is your intention to remove them, then why not just delete all your existing data and import the new weekly file?

A simple way to do steps 1 and 2 is to do a matched query between the two tables, and first delete all the matching members from your Final table. Then import all the weekly records into the Final table.

 

[Micron]

Another would be to simply CurrentDb.Execute "your sql statement here" and don't bother with dbFailOnError parameter (just for the new employees), as long as the necessary table fields are unique. When a duplicate is found, the execution should simply move on to the next employee.

Then for the Updates, you simply Update without regard to what is new or not. Anything that is the same value remains as before, otherwise it gets updated. As for the no-longer-here employees, that would require an unmatched query as noted.