Comparing 2 recordsets, then appending or updating as appropriate

[Dansam]

Hi everyone
So here's my current noodle scratcher:
I want to compare 2 recordsets (one is the current data, the other an import from a spreadsheet) both sets have a common field (membership number). If the membership numbers match, then run a query so the import set updates the current set; if there's no match then the import set appends the record to the live.
Simple I hear you cry! (or not as it turns out)
I can assemble recordsets, I can write the updates and appends all in VBA, but comparing the 2 records is making my head hurt.
Help!

 

[June7]

Perhaps you can employ a technique known as 'UPSERT'. Review https://stackoverflow.com/questions/6199417/upserting-in-ms-access

 

[plog]

No UPDATE needed, you simply need to APPEND records to those in the import.

Create a query using both the Import and the Current data, join them by MemberNo and set it so that it shows all records from the Current data. Bring down the MemberNo from the Import data and set its criteria to Null. That identifies those in the Current data and those not in Import.

Next bring in all the fields from Current and convert it to an APPEND query so that all the records go into the Import data. After that the Import data has all the data you want.

 

[theDBguy]

Hi. This probably has the same info as the one at SO, but hopefully it still helps.

Update Query with Append

I can't say how often this situation comes up in real life, but an UtterAccess member wanted to find out how to run a single query that will...

thedbguy.blogspot.com

 

[Dansam]

Thanks all for the advice, I'll have a look at the options and see which works best

 

[Dansam]

Thanks for the advice and pointer, it was a very simple solution in the end. I created an update query using LEFT JOIN (import table to live table) that updates existing and imports new records. Helped me simplify a number of import functions and cleaned up and reduced my modules

 

[theDBguy]

Thanks for the advice and pointer, it was a very simple solution in the end. I created an update query using LEFT JOIN (import table to live table) that updates existing and imports new records. Helped me simplify a number of import functions and cleaned up and reduced my modules

Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

Which file is the "master"? Is the Access database the master or is the Excel spreadsheet the master? It is really dangerous to have two different systems updating data. How will you be able to determine which record is actually the "current" one if both have been updated?

 

[isladogs]

Again, probably similar info to that given earlier: UpEnd Query - Mendip Data Systems.
You may also find this extended article useful: Synchronise Data 1 - Mendip Data Systems