>id1 id2 >1 2 >2 1>All I really want in this instance is one entry.
>SELECT Addresses.iid, Addresses_a.iid; > FROM duplicates!addresses, duplicates!addresses Addresses_a; > WHERE Addresses.iid <> Addresses_a.iid; > AND Addresses.csurname == Addresses_a.csurname; > AND Addresses.cforename == Addresses_a.cforename; > AND (Addresses.caddress_1 == Addresses_a.caddress_1; > OR (Addresses.cpostcode == Addresses_a.cpostcode)); > ORDER BY Addresses.csurname, Addresses.cforename >>Many thanks in anticipation.
Create Cursor crsDupes (myID i, surname c(10), address c(10)) lcAddress = Sys(2015) lcSurname = Sys(2015) For ix = 1 to 20 If Int(Rand()*1000)%2=0 lcAddress = Sys(2015) lcSurname = Sys(2015) endif Insert into crsDupes values (ix,lcSurName ,lcAddress ) EndFor browse title 'My Table'
Select c1.myID as ID1, c3.myID as ID2 ; from crsDupes c1 ; inner join crsDupes c3 ; on c1.myID < c3.myID and ; c1.surname = c3.surname and ; c1.address = c3.address *Or more compact Select c1.myID as ID1, c3.myID as ID2 ; from crsDupes c1 ; inner join crsDupes c3 ; on c1.myID < c3.myID and ; c1.surname = c3.surname and ; c1.address = c3.address ; where c1.myID = ; (select Min(c2.myID) ; from crsDupes c2 ; where c1.surname = c2.surname and ; c1.address = c2.address)Cetin