Comparing updated data (2000)

Topic

New Reply

Two years ago we got a list of names/addreses/phone numbers from our statewide organization. It came in a single Access table with no primary key. We added an autonumber primary key field and created additional tables so we could track activities the people participated in. This year we will get an updated list of people from our statewide organization. What is the best way to compare the new data to the old data so that without losing our connection to the activity data we can
1) delete people who no longer live in our district
2) add new people who have moved to the district
3) update addresses etc for people who are still here but have new information

The table we got from the state organization did include an ID field, but we couldn’t use that as a primary key because we had no way to generate similar numbers for people we added to the database over the two years. Also, I do not know at this time whether that unique identifier will be the same for people in the updated version that we will be recieving.

Believe me – there is likely no way to get the statewide organization to give us anything different from what they are going to give us. We’ve tried that route for six years now! Anything that gets done will be done on our end after we receive the new data from them.

Thank you for any suggestions. I should perhaps add that we are far from being code experts, although I have successfully dealt with simple code on many occasions and I love working with Access.

-cynthia

Reply | Quote

Replies

There are no really hard and fast rules for this kind of situation. Lacking a unique ID that can be used to compare people over the years, you will have to match on other fields, such as first name, last name, date of birth, address, … This will probably not be 100 % accurate, so human judgement will play a role, but with a bit of luck you may be able to process 90 % or so automatically.

Unless you are pressed for disk space, I wouldn’t actually delete records, just set a flag (Yes/No field) indicating that they are no longer “active”.

Reply | Quote

There are no really hard and fast rules for this kind of situation. Lacking a unique ID that can be used to compare people over the years, you will have to match on other fields, such as first name, last name, date of birth, address, … This will probably not be 100 % accurate, so human judgement will play a role, but with a bit of luck you may be able to process 90 % or so automatically.

Unless you are pressed for disk space, I wouldn’t actually delete records, just set a flag (Yes/No field) indicating that they are no longer “active”.

Reply | Quote

Thanks Hans
The two methods we have thought of so far are:
1) print the new list and compare the data manually (this is what my partner in this project wants to do – but we are talking about more than 10,000 names here – I’m reasonably convinced that Access won’t make this the best option!)
2) make a backup copy of the database (of course), then append the updated data and use the “find duplicates” wizard to compare people with the same names.

My question is, is there a better way? (Or, if number 2 is the best way, can anyone offer any tips to make it as easy as possible?)

Thanks,
-cynthia

Reply | Quote

Some manual processing is inevitable, but comparing the complete lists manually is far too much work.

Instead of appending the new list to the existing one, import it as a separate table. Then create a query based on the old and the new list, and join them on the fields you judge to be most likely to provide a match, for example Last Name, Date of Birth, Gender. Which fields to use exactly depends on the data. Save this query.
Create a Find Duplicates query based on this one, with duplicates on the fields from the old list. This will give you an idea how many records from the old list have more than one match in the new list. If this number is large, you should consider adding more fields to match on, if the number is relatively small, you could consider handling those cases manually. From the duplicates query, you can create a “unique cases” query by changing the condition from Count(…) > 1 to Count(…) = 1. You can use this query to update the unique matches; hopefully that will cover the large majority of cases.

Of course, you should create backup copies along the way, so that you can always undo a step if it doesn’t have the expected or desired result.

Reply | Quote

Thanks for the tips. That will be very helpful.
-cynthia

Reply | Quote

Thanks for the tips. That will be very helpful.
-cynthia

Reply | Quote

Some manual processing is inevitable, but comparing the complete lists manually is far too much work.

Instead of appending the new list to the existing one, import it as a separate table. Then create a query based on the old and the new list, and join them on the fields you judge to be most likely to provide a match, for example Last Name, Date of Birth, Gender. Which fields to use exactly depends on the data. Save this query.
Create a Find Duplicates query based on this one, with duplicates on the fields from the old list. This will give you an idea how many records from the old list have more than one match in the new list. If this number is large, you should consider adding more fields to match on, if the number is relatively small, you could consider handling those cases manually. From the duplicates query, you can create a “unique cases” query by changing the condition from Count(…) > 1 to Count(…) = 1. You can use this query to update the unique matches; hopefully that will cover the large majority of cases.

Of course, you should create backup copies along the way, so that you can always undo a step if it doesn’t have the expected or desired result.

Reply | Quote

Thanks Hans
The two methods we have thought of so far are:
1) print the new list and compare the data manually (this is what my partner in this project wants to do – but we are talking about more than 10,000 names here – I’m reasonably convinced that Access won’t make this the best option!)
2) make a backup copy of the database (of course), then append the updated data and use the “find duplicates” wizard to compare people with the same names.

My question is, is there a better way? (Or, if number 2 is the best way, can anyone offer any tips to make it as easy as possible?)

Thanks,
-cynthia

Reply | Quote