This is how I did it:
<CFQUERY DATASOURCE="Datasource_name" NAME="Dupes">
SELECT DISTINCT TableName.UniqueID, TableName.Whatever
FROM
TableName, TableName AS TempTable
WHERE
TableName.Field1 = TempTable.Field1
AND
TableName.Field2 = TempTable.Field2
AND
TableName.Whatever = TempTable.Whatever
AND
TableName.Unique_ID <> TempTable.Unique_ID
ORDER BY TableName.Whatever
</CFQUERY>
<CFSET currentID = "">
<CFSET currentND = "">
<CFSET count = 0>
<CFLOOP QUERY="change">
<!--- compare this ID with the last one --->
<CFIF ND EQ currentND>
<!--- might as well find out how many records are for the chop --->
<CFSET count = count + 1>
<!--- Here comes the scary bit, remove comments if you dare --->
<!---
<CFQUERY DATASOURCE="Datasource_Name">
DELETE FROM TableName
WHERE Unique_ID = #Unique_ID#
</CFQUERY>
--->
<!--- Oh no! What have I done?!? --->
<CFOUTPUT>
<BR>Delete record: Unique_ID = #Unique_ID#, Whatever =
#Whatever#
</CFOUTPUT>
<CFELSE>
<!--- Phew, that was a close one --->
<CFOUTPUT>
<BR>First instance of record: Unique_ID = #Unique_ID#, Whatever
= #Whatever#
</CFOUTPUT>
</CFIF>
<!--- change values for next comparrison --->
<CFSET currentND = ND>
<CFSET currentID = ID>
</CFLOOP>
<CFOUTPUT>#Count# Records Deleted</CFOUTPUT>
Cheers
Taz
----- Original Message -----
From: "Internet Gold Coast Properties"
<[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 14, 2000 10:15 AM
Subject: How To Search Duplicate Records ???
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0033_01BFA645.CE2BE280
> Content-Type: text/plain;
> charset="windows-1257"
> Content-Transfer-Encoding: quoted-printable
>
> I have a database of over 3,000 records of properties in my =
> accommodation database it is possible that there may be duplicate =
> records due to miss spelling of a property name=20
>
> is there a way in cf that I can create code to search the entire =
> database for records that have the same telephone number and hence this =
> then will determine al the duplicate properties due to different =
> spelling of their property name as the phone number for the two =
> properties will be the same=20
>
> Kind Regards
>
> Claude Raiola
> E-Mail: [EMAIL PROTECTED]
> Website: www.internetgoldcoastproperties.com.au
>
>
>
> ------=_NextPart_000_0033_01BFA645.CE2BE280
> Content-Type: text/html;
> charset="windows-1257"
> Content-Transfer-Encoding: quoted-printable
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Dwindows-1257" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.3013.2600" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT color=3D#0000ff size=3D2>I have a database of over 3,000 =
> records of=20
> properties in my accommodation database it is possible that there may be =
>
> duplicate records due to miss spelling of a property name </FONT></DIV>
> <DIV><FONT color=3D#0000ff size=3D2></FONT> </DIV>
> <DIV><FONT color=3D#0000ff size=3D2>is there a way in cf that I =
> can create=20
> code to search the entire database for records that have the same =
> telephone=20
> number and hence this then will determine al the duplicate properties =
> due to=20
> different spelling of their property name as the phone number for the =
> two=20
> properties will be the same </FONT></DIV>
> <DIV><FONT color=3D#0000ff size=3D2><BR>Kind Regards</FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=3D#0000ff size=3D2>Claude Raiola<BR>E-Mail: <A=20
> href=3D"mailto:[EMAIL PROTECTED]">info@internetgold=
> coastproperties.com.au</A><BR>Website:=20
> <A=20
> href=3D"http://www.internetgoldcoastproperties.com.au">www.internetgoldco=
> astproperties.com.au</A></FONT></DIV>
> <DIV> </DIV>
> <DIV><FONT color=3D#0000ff size=3D2></FONT> </DIV></BODY></HTML>
>
> ------=_NextPart_000_0033_01BFA645.CE2BE280--
>
> --------------------------------------------------------------------------
----
> Archives: http://www.eGroups.com/list/cf-talk
> To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
>
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
