Rooting out duplicates?

[HenHouse]

Hi folks;
Typical - you design a flashy database, write a manual with a big section on how bad duplicates are and how to avoid them, and somehow everybody ignores it. So now I have 1800-odd records and I know that some of them refer to the same thing.

Basically it's a database that refers to educational institutions. But in it I've found unique records for Sheffield University, The University of Sheffield and Sheffield Uni. Each of these is now having client and contract data entered alongside it, depending purely on whichever one a new user stumbles upon first. Does anyone know how to find these kinds of duplicates? I can merge the records by hand but first I have to find them! I imagine the best way would be to look for all the institutions that share words in their title with another institution, minus certain ones like "University ", "College", "The", "Of" and so on.

Anybody have any idea how I can pull this off?

Thanks!

[Denburt]

I use a search routine that helps prevent this. When they go to add a new record it will search the table (minus certain words) and provides them with a list if something is similar. If they still want to add it then let them do it. This really helps with job security. ;)

Seriously though I usually clean this sort of thing up with an update query changing the criteria to fit as needed. Hope this helps if you need more details let me know.

[HenHouse]

Hi Denburt;
I really like the sound of that first method, how did you pull that one off? Is it a bit like a lookup field but cleverer?

[Denburt]

Sorry I am a bit slow on this one. I use the NotInList event to call another form with a list. Simply filter the second forms listbox as needed (newData is great for this), using wildcards as you see fit. I do this in a function as the rowsourcetype and a table to store records for things not to look for then simply do a replace in the function and Bamn. You could also use a query and just rebuild it using the querydef method as well, many ways to perform such a feat.

If they see the one the want simply have a message telling them to double click or click somewhere to add a new one. Then requery and fill in the combo box on the first form.

Is this what you were interested in and does this help?