Updating and Appending a new table

[SalmonDB]

Hi Everyone,

I'm having a bit of trouble with some tables that I was hoping you could help me with. I have three tables. The first is a large spreadsheet of data from another agency that I don't want to make any changes to (I'm just linking to this table). My next table is 'additions', records which I think should be added to the main table, I also have another table, 'corrections', for cases where I believe that the records in the main table are incorrect.

My question is this: how can I create a 4th table that would display all of the records, but replace the ones that are incorrect and append the ones that are missing. In other words, my corrections would take precedence, but when I haven't entered a correction, the original record would appear.

The original table has a unique ID number for each record, and my 'corrections' table has the same ID number, whereas the 'additions' table has an ID field that I left blank.

I attached an excel file where each of the sheets represents a table, and the final sheet represents the table I would like to produce.

Am I right in assuming the best way to deal with this would be to write some VBA code that will do each of the steps? I'm especially unsure of how to replace entire records in the new table.

Any help is very much appreciated!

Erica

 

[SalmonDB]

Thanks Pat!

I have a follow-up, when you say that the number I show as an ID won't work, is this because I don't give one to new records before appending them? I can't see any problem with doing an update query based on these ID numbers, since each record has a unique number (it is not an autonumber field, as I understand this is a problem). What if I included a step (in some code or macro in a form) where I determine the max ID number in the original table and then inserted the next ID in the sequence in the new records table, before appending to the final table? Any idea how I would write that in VBA? I could then order the table by date and location instead of ID number.

Maybe I'm over-thinking this...
Thanks again for your help!
Erica