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
--
-Larry-
--
"cebAccess" <u27504@uwe> wrote in message news:67458fa091673@uwe...
> 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!
>
|
Similar Threads
