Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-08-2010, 01:45 PM   #1
juanthomas
Registered User
 
Join Date: Mar 2010
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
juanthomas is on a distinguished road
Compare and Update Records

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?

juanthomas is offline   Reply With Quote
Old 03-08-2010, 04:48 PM   #2
ajetrumpet
Banned
 
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 99 Times in 46 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Re: Compare and Update Records

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 Code:
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 Code:
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.
ajetrumpet is offline   Reply With Quote
Old 11-05-2012, 11:32 AM   #3
lstein4
Newly Registered User
 
Join Date: Nov 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
lstein4 is on a distinguished road
Re: Compare and Update Records

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 !

lstein4 is offline   Reply With Quote
Reply

Tags
code , compare , import , tables

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare Records from two tables JerryWoodstock General 3 12-31-2014 02:40 AM
Survey Stats Converter Update Issues. rsmonkey Modules & VBA 2 08-24-2007 06:03 AM
Update several fields and compare strings gasbored Queries 2 08-10-2007 03:04 AM
Update and Compare table records MartinaQ Tables 3 09-01-2005 12:20 PM
OnClick SQL Select -> Compare -> SQL Update m82palmer Modules & VBA 2 04-04-2005 02:30 PM




All times are GMT -8. The time now is 01:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World