Comparing fields and records and updating records

[cebAccess]

I am working on an Oil and Gas Access database in which the company Imports
MS Excel files that consultants fill in while they are working out in the
field. The Excel file contains duplicate names for different interest
amounts that the owners have in the property, gas, and oil.

The Company owner wants me to compare the names and address information to
look for duplicates and possible discrepancies. One record may have John T.
Smith and another may have John Smith and another may have J. Smith. I need
to be able to compare the various name and address fields and show
approximate matches. The owner wants to be able to see the apparent matches
and have a user decide which fields contain the information that is correct.
He wants to choose a field that is correct (John T. Smith) and have other
records update to the information in the chosen correct record.

The owner has also said that one record may be correct on the name, and a
different record may be correct on the address. He wants to be able to
choose which field is correct in the record and update a final record with
the selected information. The end result of a corrected record may have the
First, Last, and Middle initial for the name taken from one record, and the
address field may come from a different record. He wants to choose a field
that is correct and have the database take that information and update a
final correct record.

First Last MI Address City State Zip
1. John Smith T. 123 Main Norman OK 73070
2. John Smith 124 Main Norman OK 73070
3. Jon Smith 567 Main Norman OK 73069
4. John Smith T. 555 Main Noman OK

For example, #1 has the correct First, Last, MI for name. #3 has the correct
city and zip, and #4 has the correct address. As the end result he wants to
choose the correct info in a field in the record and update a final record
to: John Smith T. 555 Main Norman OK 73069.

After this initial comparison the data is then divided into tables and only
the one correct name and address is used and a primary key is defined. Then
the next Excel file is imported and it will have names and addresses that
need to be compared with the “new†existing table stored in Access. Go
through updating the fields again and then append them to the table in Access.


I have been searching Access forum after Access forum for something close to
this example and have had no luck finding anything close. Please help if you
can! Your help will be VERY, VERY, VERY much appreciated!

 

[Jamie Collins]

The Company owner wants me to compare the names and address information to
look for duplicates and possible discrepancies.

Has your boss considered experienced professional help <g>? Address
data is a nightmare, IMO. Try a google search for 'data hygene'.
Although I cannot vouch for any, the provider I see mentioned most
often is Melissa Data.

Jamie.

--

 

[Larry Daugherty]

Hi,

I was pretty sure that I'd seen this post or one similar in recent
months or years but googling on "Oil and Gas database" didn't flush it
out ...

One very good reason that you haven't found a solution to your stated
problem is that your (your boss's") view of the problem is too narrow.
The problem you have stated can be solved in a way that's pretty close
to what you have described. However, to do so would be like putting
racing fins on a buggy whip. If you had a solution that worked as you
stated you would still have a labor intensive, error prone system,
just as you now have.

In a properly designed and implemented system, the particular
ambiguous data issues that are driving you mad are non-issues.

To arrive at a properly designed system you first have to analyze the
problems it will have to address. That means analyzing the existing
process from end-to-end and understanding it before trying to solve
parts of it.

It's obvious that the existing system's "errors" or ambiguous data
originates out in the field with the entry of information by
consultants into their Excel spreadsheets.

If your company culture or your consultants dictate that Excel, not
Access will be used in the field there are ways to significantly
improve the Excel parts of the process.

HTH