I have two table in access database named Tablenew and Tableold
I need to compare field1 from Tablenew table with field3 of Tableold, field1 from Tablenew table with field3 of destfield4 from Tableold table with field6 of Tableold....ecc if one or more records are changed update the tableold with new data of Tablenew...
Possible with a query?
sure. assuming this is a one time thing (approach might vary if repeating)...
make a query that has these two tables with a join between fields that must be identical in order to compare - - - there must be some ID field that is identical in both tables so that you are sure that record "1" of each table is matched with the correct record "1" of the 2nd table.
then add the fields you want compared - side by side onto the query i.e. New Field1 then Old Field 3. After these two create a 'calculated field' with something like this:
(If you are not familiar with iif statements you'll want to look that up in Help, or an Access textbook, or google about...)
Then your last step is to make a brand new MakeTable query that uses this query - and just select the Final Fields to use to make the new table. - - where you can make a brand new table to replace the "new" table....or a little more tricky but still possible is to make an UpdateQuery of the new table....really depends on how comfortable you are with the technology.....