I have a large inventory database.
There is a table of type of object eg:
"4 drawer Filing Cabinet".
There are multiple duplicates of this due to misspelling
"4 drawer Filing Carbinet"
"4 drawer Filling Cabinet"
"4 draw Filing Cabinet"
The primary key of this table has then been used in other places in the database to assign other information.
I would like to have a form where one can identify duplicates such as this in a database.
There would probably be a list box where the user could assign several of them as equivalent.
The user would then declare one of the variations "correct"
The form would then run an update query on all the foreign keys that used the incorrect variations and update to the "correct" primary key.
I then want to delete (or probably in proper database design declare redundant) all incorrect variations.
This would then eventually allow for properly normalised data.
I understand the principles of how this would be done. (Filter datasheets from test boxes, generate update queries reading the primary key off, search the database for uses of the primary key.) However, building this would take me a very long time.
This seems such a fundamental data cleaning task I would have thought there would be multiple templates and examples. However, I haven't found any - maybe it is just my bad searching.
Any help would be appreciated.
There is a table of type of object eg:
"4 drawer Filing Cabinet".
There are multiple duplicates of this due to misspelling
"4 drawer Filing Carbinet"
"4 drawer Filling Cabinet"
"4 draw Filing Cabinet"
The primary key of this table has then been used in other places in the database to assign other information.
I would like to have a form where one can identify duplicates such as this in a database.
There would probably be a list box where the user could assign several of them as equivalent.
The user would then declare one of the variations "correct"
The form would then run an update query on all the foreign keys that used the incorrect variations and update to the "correct" primary key.
I then want to delete (or probably in proper database design declare redundant) all incorrect variations.
This would then eventually allow for properly normalised data.
I understand the principles of how this would be done. (Filter datasheets from test boxes, generate update queries reading the primary key off, search the database for uses of the primary key.) However, building this would take me a very long time.
This seems such a fundamental data cleaning task I would have thought there would be multiple templates and examples. However, I haven't found any - maybe it is just my bad searching.
Any help would be appreciated.