I've noticed a couple of posts about this subject, however in the contexts they arent helping me much.
I have a table of data with the unique item being a reference number. There are several fields of data for the reference number and there can be also be multiple entries for a reference number if the fields are changed at a later date.
Each month we get a new set of reference numbers which are loaded into another table. What I need to do is check the new list of reference numbers against the old one that contains all the data, and if theres a match, insert the latest data into the new table.
1584541228 | | | |
4548645454 | | | |
For ref 1584541228 I would need to insert the info from the archive table into the live table for just the entry date 19/11/2009, and 4548645454 all the data as theres only one record for that one.
I've been playing around with Append and Update queries but cant quite get it right. The nearest I got, I got an error with the SQl saying "subquery without EXISTS statement". There must be an easier way, and im at a dead end.
Correct me if I am wrong, but you are never INSERTING data in the new live table, right? You are only UPDATING the live table with information from the archive table, if the reference number matches with a number in the archive table.
And then, if you find a match, you want to grab the latest information from the archive table, and update the live table with that. Is that about correct?
Hmmm. I am having problems writing the sql statement for this (which is probably exactly what you have been encountering to begin with...).
I am at work right now, so I can't dedicate the time to it that it needs. I will look into this again this evening, but it should be a fairly easy sql query.
One additional question though: does each row have a unique ID (autonumber) in both tables?