merging similar records?

[dizzle]

i am programming a GUI for an old inventory system. One of my main problems with this database i have been given is the fact that a record for an inventory item may have been entered once or five times. just depending on how the previous users saw the information since there was no data integerity set up in the past. I was wondering if anyone had heard of a way that you can show the user multiple similar records as i have described and allow the user to merge these two, three, four, etc records together? if so, how would i go about approaching this?

 

[SQLSister]

Just how would identify them as similar records? What fields would you need to identify it? If you don;t have any natural unique key fields in your current field structure, this would be impossible to do. If you have them and they just didn;t bother to enforce uniqueness, then it is possible. You would want to do a group by on the those fields and then select the ones with a count of greater than 1.

Select Make, Model, SerialNo, count(*) from Table1
Group By Make, Model, SerialNo
Having Count(*) > 1

Then make this a dervied table and join to the intial table to see the records with Multiple counts. Something like:

Select Table1.* from Table1 join (Select Make, Model, SerialNo, count(*) from Table1
Group By Make, Model, SerialNo
Having Count(*) > 1)a
on Table1.Make = a.Make
And Table1.Model = a.Model
and Table1.SerialNo = a.SerialNo

 

[dizzle]

well what i was thinking about doing is selecting all from the table, and listing the items with checkboxes and let the user go through it manually and decide for themselves what records are similar, choose the ones they want to edit, then bring them up on another page. once they make the changes the old ones are deleted, and new one inserted

 

[SemperFiDownUnda]

God I hope you don't have many records.
If you have 1000 records and the person is looking at record 850 are they going to remember that record 173 is similar?

Like SQLSister says you have to deside what your definition of "Similar" is.

If you can't come up with a manual process don't expect to automate it.

You could play with things like SOUNDEX to try to catch different spellings/misspelling of the same word.

I'd say figure how you would determine what constitutes a records to be "Similar". Produce a distinct list of Over all "Similar" records. They pick one of these they'll see a list of records that are in that "Similar" Batch. Let them select some and elect to do what you want. Delete them out and have a new one inserted in place of them.



Hope I've been helpful,
Wayne Francis

If you want to get the best response to a question, please check out FAQ222-2244 first