delete records and modufy records

[Guest]

Hello,

I have 2 questions.

(1) table1 and table2 has same data structure.
I like to delete duplicate records in table1 and table2.
i.e. table1 and table2 will not have same records. table1 will be the main
table.

(2) table3 and table4 has same data structure.
table4 has few records but has more information than table3.
Can I use query update from table4 to table3?
I can not use append, because both tables have same records, table3 has more
records than tables4.

Thanks

 

[Michel Walsh]

1- by duplicate, you mean present and in table1, and in table2, but no more
than once in each table? and then, deleting them from table2?

DELETE DISTINCTROW table2.* FROM table1 INNER JOIN table2
ON table1.f1 = table2.f1 AND table1.f2=table2.f2 AND
table1.f3=table2.f3 AND ...




2- UPDATE table3 RIGHT JOIN table4 ON table3.id=table4.id
SET table3.id=table4.id,
table3.f1=table4.f1,
table3.f2=table4.f2,
...

(for all fields that table3 owns, and that table4 can supply)




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks

1- by duplicate, you mean present and in table1, and in table2, but no more
than once in each table? and then, deleting them from table2?

DELETE DISTINCTROW table2.* FROM table1 INNER JOIN table2
ON table1.f1 = table2.f1 AND table1.f2=table2.f2 AND
table1.f3=table2.f3 AND ...




2- UPDATE table3 RIGHT JOIN table4 ON table3.id=table4.id
SET table3.id=table4.id,
table3.f1=table4.f1,
table3.f2=table4.f2,
...

(for all fields that table3 owns, and that table4 can supply)




Hoping it may help,
Vanderghast, Access MVP