Compare Tables for changes

[Binnit]

There are a number of posts relating to this issue but none quite seem to show the complete answer, most just appear to suggest the Unmatched query solution which is OK if thats what you want on a given field.

My problem is that I have a table containing approx 40 fields which is used for updating and adding new records. Every month, I copy this table and need to compare it with the previous months table to identify ALL changes in ALL fields - not just new records.

The structure of the 2 tables are exactly the same, basically I want a query that reports all records that have "changed" and or been "added".

Is there a quick solution to this? I have tried various suggestions in the other posts but none seem to quite deal with it on a table with more than a few fields.
Thanks in advance

If IT ain’t working Binnit and Reboot

 

[hermanlaksko]

The only thing I can come up with is a loop comparing the 2 recordsets and saving or changing poss diff's, but i am sure that you must have thourght of this allready.


Herman
Say no to macros

 

[Binnit]

hermanlaksko
No this is not something I had thought of, I was rather hoping that there was a standard query building solution to this.

This may well be my only option, however, I am not very experienced with the constructs of multiple tables & nested loops (I guess thats what it would need)do you have any samples of how you would start building this?

Another thought has occurred that it would need to be done in reverse as well to check for records in the old table that have been deleted and no longer exist in the new table.



If IT ain’t working Binnit and Reboot

 

[PHV]

Hopefully the 2 tables have a common primary key you may rely on ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hermanlaksko]

Hi Binnit

Ahh.. no I have no examples on doing this other than a standard Set Re=DB.Openrecordsource("..... etc.

To do it in this case I would need the database itself and fiddle with it for some time so check for speed etc.

Herman
Say no to macros

 

[Binnit]

PHV
Yep.... PK's in place.

Tables are:-

tblFolioDetails (current table)
tblPreDecFolioDetails (previous month table)

PK field in both cases is:-
FolioRef

Any ideas how I construct the loop as per hemanlaksko suggests?

If IT ain’t working Binnit and Reboot