query problem???

[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
..

 

[Tom Ellison]

Dear Linda:

Deleting the rows from both tables would seem to be a real challenge.
That is because this would require two DELETE queries. However, once
the rows have been deleted from one table, there is no way to find
which rows must be deleted from the other. They can only be
identified in that they are duplicated in the other table, and once
they are removed from the other table, this identification is not
possible.

Several ways of accomplishing this occur to me.

You say you have a relationship established between the two tables
based on all the rows in both tables. Possibly you could add cascade
deletes to this relationship so that when you delete rows in one table
the related rows in the other table are deleted as well.

Another option is to add a boolean column to both tables so you can
UPDATE this to mark those duplicated rows for deletion. You could
then perform this marking in both directions, then come through and
delete from both tables.

If you were able to establish a relationship between the two tables,
is it then the case that all the rows in Table A are duplicated in
Table B? If so, then you could select the rows from B to be deleted,
then just delete all the rows from A unconditionally.

You could build a third table of the duplicates and use this to delete
from A and B, then delete everything from this third table.

I will add only that your table structure and methodology are quite
troubling. Without knowing on what your design is based and what you
are trying to accomplish logically, it is not possible to say more
than this.

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
.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts