Compare and Update Records (1 Viewer)

juanthomas

New member
Local time
Yesterday, 18:10
Joined
Mar 8, 2010
Messages
1
I am using MS Access 2007. I have created an Address Master Table (Master) from a .csv file.

I frequently get updates (new addresses or changes in address information) to the .csv file. To help with the import process, I created a temp table (tmpCSV); using compare queries I can identify any new records and append them to the "Master" table.

However, the addresses, or phone numbers may change for existing records within the "Master" table.

I want to build a query or VB code that will go through the tmpCSV table and identify changes within columns and then update only the records with changes.

Does anyone have any sample code or suggestions?
 

ajetrumpet

Banned
Local time
Yesterday, 20:10
Joined
Jun 22, 2007
Messages
5,638
how big are the two tables? if they're not THOUSANDS of recs long, dlookup might be fine.

do you have a unique identifier for your records in both tables? like a name? an id field?


if you do, maybe something like this would work for a query:
PHP:
UPDATE master SET

address = iif(dlookup("address", "temptable", 

"[uniquefield] = " & [uniquefield]) = [address], [address], 

dlookup("address", "temptable", 

"[uniquefield] = " & [uniquefield])),

DO THE SAME THING FOR THE PHONE HERE

but then again, if the unique field is always the same between both tables, you can certainly do updating even if the value remains the same....and save some processing time:
PHP:
UPDATE master SET

address = iif(dcount("uniquefield", "temptable") > 0, 

dlookup("address", "temptable", 

"[uniquefield] = " & [uniquefield]),  [address]),

phone = iif(dcount("uniquefield", "temptable") > 0, 

dlookup("phone", "temptable", 

"[uniquefield] = " & [uniquefield]),  [phone])


be aware though that using dlookup anywhere in a query when your tables are in the thousands or more for count of records will yield minutes of processing time instead of seconds. it's an incredibly slow function, and usually a last resort with me because of that.
 

lstein4

New member
Local time
Yesterday, 21:10
Joined
Nov 5, 2012
Messages
1
Does anyone know of a way to do this easily when thousands (a few hundred thousand actually), where I have a unique identifier. I need to compare the delta file for a "hit" on the unique identifier, if I get one replace all the data in the record, if I dont get a hit, add the record as a new record. Thoughts anyone. Is there SQL code anyone has made, for prior projects or even a good add-in ? Any help is appreciated !
 

Users who are viewing this thread

Top Bottom