>1. Is either table part of a database?
>
>JCT- THE TABLE BEING COMPARED AGAINST IS PART OF A DATABASE. THE OTHER IS A FREE TABLE
>
>2. Is one table much larger than the other?
>JCT - THE TABLE BEIGN COMPARED AGAINST STARTS OFF BEING LARGER BUT AS THE MONTHS GO BY THEY EVENTUALLY EVEN OUT...THE TABLE THAT I'M COMPARED IS A CUMULATIVE TABLE THAT ADDS TO MONTH BY MONTH
>
>3. What do you intend to do with the duplicates?
>JCT - I WANT TO DELETE THE DUPLICATES AND DO NOTHING WITH THEM. ONLY IF THE RECORDS ARE NOT DUPLICATE (AT LEAST ONE FIELD IS DIFF) BETWEEN THE TWO TABLES, THEN I WILL REPORT THOSE
>
>4. How many fields are there?
>JCT - THERE ARE 32 FIELDS
OK... here's a go at it:
LOCAL ARRAY laFields
LOCAL lcOn, lnI
lcOn = ""
SELECT <either table>
FOR lnI = 1 TO AFIELDS(laFields)
lcOn = lcOn + IIF(EMPTY(lcOn),"ON "," AND ")
lcOn = lcOn + "FreeTable." + laFields[lnI,1] + " = DBCTable." + laFields[lnI,1]
ENDFOR
SELECT FreeTable.* ;
FROM FreeTable ;
LEFT JOIN DBCTable &lcOn ;
WHERE FreeTable.Field1 # DBCTable.Field1
The resulting cursor will contain only the records from the FreeTable that couldn't be matched with a record in the DBCTable. Replace Field1 in the WHERE clause with any field name - in the join, the unique records will have all fields in DBCTable = .NULL.
You should be aware that in order to Rushmore optimize the query, you'd need an index on every field in both tables.
Brien R. Givens
Brampwood Systems