duplicate records

[linda]

hello
I have two tables A and B.
Table A has 500 records
eg field1 field2 field3 field4 field5)
1 20 68 98 200
and so on ......
Table B has 34000 records
eg field1 field2 field3 field4 field5)
14 19 100 198 199
and so on ......
Both tables have same records in each other.

I would like to make a query that can show the duplicate
records in both Tables A & B for eg (table A record 1 =
table B record 5) and delete the duplicate records in
both the tables.

I have made the tables relationships every field with
every field.
And made the query showing both the tables and their
fields with duplicate records( means same records in
Table A and B)
It's fine but how can I delete the duplicate records from
both the tables after the query shows the result.
I would really appreciate if someone could help me with
this problem.
Thank you

 

[Michel Walsh]

Hi,


To be safe, it is a case where you need a virtual table. Indeed, if you
delete the records in tableA, then, when you would look at tableB, you won't
find any more any record matching in tableA. So, the logic would be:

Find the matching records in both table, put them in a temp table.
Delete the records common in tableA and in temp.
Delete the records common in tableB and in temp.
Drop the temp table.

INSERT tableA.* INTO temp
FROM tableA INNER JOIN tableB
ON (
tableA.f1=tableB.f1
AND
tableA.f2=tableB.f2
AND
tableA.f3=tableB.f3
AND
tableA.f4=tableB.f4
AND
tableA.f5=tableB.f5
)

that creates table temp.


DELETE DISTRINCTROW tableA.*
FROM tableA INNER JOIN temp
ON (
tableA.f1=temp.f1
AND
tableA.f2=temp.f2
AND
tableA.f3=temp.f3
AND
tableA.f4=temp.f4
AND
tableA.f5=temp.f5
)


delete the records common in temp and in tableA. Do something similar for
tableB.


DROP TABLE temp


to eradicate the temp table.



I would suggest to package the whole sequence in a transaction to make
the whole manipulation appears like "one" statement that globally succeed,
else, if there is an error at some point, nothing is deleted at all.



Hoping it may help,
Vanderghast, Access MVP

 

[linda]

THANK YOU SIR
I figured out how to delete the duplicate records.
Can I see the unmatched records without deleting them,
using find unmatched query.
I tried to find unmatched records( table a records
unmatching table b)
it doesn't work
How can I do it. That is find duplicate records and un
matched records( showing the fields as listed below)
without any deletion.
Thanks once again.
bye