Hi, could someone plesase give me a clue as to how i can find out if i have duplicate records in a table for a field called Address.

Many thanks

John

John


Its not trivial. If they are exact matches you can use the HAVING clause (there are examples in these ngs) but if you want to catch things like

Main Street
Mian Street
Main St
Main St.

9 Saxon Way
9, Saxon Way
9
Saxon Way

its more difficult.

What level of duplication are you trying to detect?

Roy Lambert

John,

You can try something like:

SELECT DisplayName Dupname, COUNT(*)
INTO memory\temp FROM Clients GROUP BY
DisplayName HAVING COUNT(*) > 1 ;
SELECT DisplayName,ClientID from Clients
JOIN memory\temp on DisplayName = Dupname
ORDER BY DisplayName;

clientid would be a unique identifier for reference.

Regards,
Scott.

"john.s" <john.s@hotmail.com> wrote in message
news:B2AE9C01-E6C2-4E8B-8F70-185CBA3BE455@news.elevatesoft.com...
> Hi, could someone plesase give me a clue as to how i can find out if i
> have duplicate records in a table for a field called Address.
>
> Many thanks
>
> John
>

Roy,

<< Its not trivial. If they are exact matches you can use the HAVING clause
(there are examples in these ngs) but if you want to catch things like >>

A custom SQL function could solve that also:

http://www.elevatesoft.com/dbisam4d5_customizing_engine.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

><< Its not trivial. If they are exact matches you can use the HAVING clause
>(there are examples in these ngs) but if you want to catch things like >>
>
>A custom SQL function could solve that also:

Possible but a bitch to write. I was involved with deduping applications when a couple of privatisations (eg British Gas) took place and its horrendous.

With everything we threw at it in terms of fuzzy matching there were still thousands of records we had to eyeball.

Roy Lambert

Roy,

<< Possible but a bitch to write. I was involved with deduping applications
when a couple of privatisations (eg British Gas) took place and its
horrendous.

With everything we threw at it in terms of fuzzy matching there were still
thousands of records we had to eyeball. >>

Sure.  I was just pointing out that it could do a little more complex
matching than straight SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com