I need to change many records in an Access database. Thanks to a previous thread in this forum, I am able to create a query which I can use to edit the database. Now I have two questions:
1) In the query design grid I want to use a function to select records based on a portion of the text in a field. For example, if the records are
abcde
abcdf
fdcba
I want to show only the records that begin with 'abcd'.
2) Next, I want to change 'abcd' to 'gh' in each of these records.
How can I do this? There are several thousand records, so it is not practical to change them one by one.
Thanks for your help.
Changing MS Access database records
- DrewJensen
- Volunteer
- Posts: 1734
- Joined: Sat Oct 06, 2007 9:01 pm
- Location: Cumberland, MD - USA
Re: Changing MS Access database records
Wellllll
The first thing is that Base does not have a nice GUI based query tool that supports update, insert and delete queries.
Which means that you must use the correct SQL commands, for the database you are working with - in this case MS Access - in the SQL window in order to do this type of mass update.
So - lets break this down.
I have a mdb file here and I added a table - Table1( ID, txt_val ) I'll use that for the example.
step one - find the proper records.
For this you can can use a LIKE command.
SELECT ID, txt_val FROM Table1 WHERE txt_val LIKE 'abcd%'
The * is a wild card that matches to 0 or more characters, so it will find all the records where val_txt starts with 'abcd'.
Great you had that much already though.
Step two - the SQL update command.
UPDATE Table1
SET txt_val = <some_value>
WHERE txt_val LIKE 'abcd%';
Ok that is the generic structure of the update command - but now you need to construct <some_value>
Step three - build the new value.
MS Access in SQL mode supports the following string functions: LEFT, RIGHT, MID, LEN AND INSTR
Here is the statement then that will do what you wanted.
In my case I just open the SQL window, Tools > SQL, copy and paste that into the command window - click 'execute'. The result box tells me it ran successfully - open Table1 and I now have
ghe
ghf
fdcba
The first thing is that Base does not have a nice GUI based query tool that supports update, insert and delete queries.
Which means that you must use the correct SQL commands, for the database you are working with - in this case MS Access - in the SQL window in order to do this type of mass update.
So - lets break this down.
I have a mdb file here and I added a table - Table1( ID, txt_val ) I'll use that for the example.
step one - find the proper records.
For this you can can use a LIKE command.
SELECT ID, txt_val FROM Table1 WHERE txt_val LIKE 'abcd%'
The * is a wild card that matches to 0 or more characters, so it will find all the records where val_txt starts with 'abcd'.
Great you had that much already though.
Step two - the SQL update command.
UPDATE Table1
SET txt_val = <some_value>
WHERE txt_val LIKE 'abcd%';
Ok that is the generic structure of the update command - but now you need to construct <some_value>
Step three - build the new value.
MS Access in SQL mode supports the following string functions: LEFT, RIGHT, MID, LEN AND INSTR
Here is the statement then that will do what you wanted.
Code: Select all
UPDATE `Table1`
set `txt_val` = 'gh' & right( `txt_val`, len( `txt_val` ) - len('abcd') )
WHERE `txt_val` LIKE 'abcd%';
ghe
ghf
fdcba
Edit: One last thing - be sure to make a copy of your database before you start your mass updates - the changes are permanent when done this way using SQL - there is no roll back function as there is with the MS Access update GUI based query - when you click execute it can! |
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Re: Changing MS Access database records
Drew - Thank you very much for your detailed answer. It looks like exactly what I needed to know. I will let you know how it works.
Thank you!
Ken
Morgantown WV
Thank you!
Ken
Morgantown WV