Help: Cleaning Messy Table Data

[Guest]

My mind is running in circles on itself trying to find a solution, or series
of solutions to this issue. Any assistance is deeply appreciated.

I have a table, which gets sent to me from a contracted vendor as a
spreadsheet, and I have to 'clean' the table of human errors (essentially).
The table is a complete file that replaces the previous one within my
database, so simply modifying the data will not produce a lasting change.

The table contains these fields:
- ID
- Store
- Last Name
- First Name
- File Open Date
- File Status
- File Value

The table is supposed to only have 1 ID for each Store/Last/First
combination, and there is only supposed to be 1 occurance of the ID in the
table.
The table contains these issues:
- Records with more than 1 ID for each Store/Last/First combination
- Records with more than 1 Store/Last/First combination for 1 ID
- Records which duplicate ID/Store/Last/First rows, because they have more
than 1 status, value, or date listed.

We are working with the vendor to correct issues, but we are having limited
success since their process is fundamentally flawed (I would fire them if I
could).

What I need to accomplish with this file is to distill it down so that I can
query against the ID number, and be 100% sure of the information returned. To
accomplish this, I need to remove all duplicate IDs with different names,
duplicate names with different IDs, and duplicate IDs with duplicate names,
return the max of the file date, and the status and value associated with
that record.

I do not have any issue removing the duplicated information from inclusion
in the final table, as these are the records that we need to be constantly
returning to the vendor for him to fix.

I can get any 1 part of this process completed, but getting them all
completed together, or joining them together after completing them
individually is getting really messy, and I am stuck.

Any assistance with direction or methodology I need to be considering is
greatly appreciated.

 

[Guest]

My suggestion would be that you have a table of clean data with unique ids
etc and then run an append query of any new data you can make the id, first
name and last name unique.

The second stage would be to run an update query each time to update

- File Open Date
- File Status
- File Value

you would have three joins in your query from your clean table linking ID,
firstname and last name to insure integraty.

Hope this helps