Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing updated data (2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing updated data (2000)

    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".

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing updated data (2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Comparing updated data (2000)

    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.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Comparing updated data (2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •