compare two tables, add records that are different

[ggreg]

I need code or help getting started with code that will
compare two tables looking for any changes in fields, the two
tables have the same fields, one table is called hist and another is
called changes, consider my two tables have 3 fields name A, B and C. While A is like the Id of the records A can not be a key field. So if there is a record in field A in the table change that is not in hist then I need to add those records to hist. now if there
are any differance in fields B or C from change table compared with hist tables then I need to add those records to the hist tables.


While I am asking for help using an example with 3 fields I have over 10 fields or otherwise I could make all the fields key fields and use an append query. The other problem I am running into
is that my fields have nulls or I might have used the unmatch query. These two tables need to be compare several times a year.

can someone help me with this code?

Thanks in advance

 

[dcx693]

Sounds like you need an update query or some variation of it. Check out this link: Updating and Appending Records in One Update Query .

 

[ggreg]

The append query would work for fields A......but the update
query is not what I can use because I do not want to update
the values in fields B and C, I want to add the records so a person
can view all the changes that have taken place. In field A
say the value is 1, There could be several records for 1.

Thanks for the response

 

[dcx693]

Yeah, sorry about that. You'll just need an append query, where you find records that exist in one table but not the other as well as records that do exist but have changed.

Attached is a picture of a select query that compares three fields per table and finds those that either did not exist before or that have changed in some way. Just change it to an append query and it should work for you.

 

[ggreg]

dcx,

Thanks for posting that picture, I see now how to set up
the append query will work on it and come back and tell
you how it works out. I need that picture!