Comparing 2 recordsets, then appending or updating as appropriate (1 Viewer)

Dansam

New member
Local time
Today, 12:05
Joined
Apr 12, 2021
Messages
17
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!
 

plog

Banishment Pending
Local time
Today, 07:05
Joined
May 11, 2011
Messages
11,613
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

I’m here to help
Staff member
Local time
Today, 05:05
Joined
Oct 29, 2018
Messages
21,358
Hi. This probably has the same info as the one at SO, but hopefully it still helps.

 

Dansam

New member
Local time
Today, 12:05
Joined
Apr 12, 2021
Messages
17
Thanks all for the advice, I'll have a look at the options and see which works best :)
 

Dansam

New member
Local time
Today, 12:05
Joined
Apr 12, 2021
Messages
17
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

I’m here to help
Staff member
Local time
Today, 05:05
Joined
Oct 29, 2018
Messages
21,358
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

Super Moderator
Staff member
Local time
Today, 08:05
Joined
Feb 19, 2002
Messages
42,981
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?
 

Users who are viewing this thread

Top Bottom