pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Bit confused here.
I have a table that I need to update 300+ records by changing a number in a field (for argument sake field B). I have a list of the records I need to update as a text file (and thus imported as another table). How do I make Access look at the text file/imported table records and update just those in the master table?
Hope that makes sence...
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
If you have both a key and the new values in the text file you can either -
chop the text file about and make a whole heap of UPDATE statements.
chop the text file up and make one update statement using WHERE IN if Access supports it? Messy that.
import the text file as a new table then do a JOIN to get the resultant query looking like how you want it, then CREATE TABLE AS your new query.
If I'm doing anything fairly large I typically go for option three, you can see exactly the changes you're about to commit and you don't alter the data, you make a copy of it, then when you're happy you can remove the old table and rename your new one to replace it.
Might be a ball ache with relationships etc. though, that is one of the downsides.
|
Ian W
Member
Registered: 8th Nov 03
Location: Wirral, Merseyside
User status: Offline
|
Did you sort this pow?
I can send you an example of one of mine if that would hep?
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Ian (W) that would be awesome 
|
Ian W
Member
Registered: 8th Nov 03
Location: Wirral, Merseyside
User status: Offline
|
Here you go.
MasterTable - Table to be updated
Imported - Table to match the 300 records
Field1 - Matching data in both tables.
Field2 - Value you want to update.
UPDATE MasterTable M
INNER JOIN Imported T on M.Field1 = T.Field1
SET M.Field2 = 'Updated Value'
Hope that makes sense.
[Edited on 02-02-2012 by Ian W]
|
Home
