Access - Update Query Help - Corsa Sport - for Vauxhall and Opel Corsa B, Corsa C and Corsa D

Not logged in [Login - Register]

You Are Not Registered Or Not Logged In

Corsa Sport » Message Board » Off Day » Geek Day » Access - Update Query Help

New Topic

New Poll

Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.

pow

Premium Member

Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline

19th Jan 12 at 14:35

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

19th Jan 12 at 14:40

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

21st Jan 12 at 00:53

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

2nd Feb 12 at 14:18

Ian (W) that would be awesome

Ian W

Member

Registered: 8th Nov 03
Location: Wirral, Merseyside
User status: Offline

2nd Feb 12 at 22:44

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]

New Topic

New Poll