Search across Columns and replace text

Question

I have an Access database of information where I need to replace text that may reside in 1 of 10 columns. I have a number of different requests for find and replace that need to be done. I need to do this twice a day.

These are the details. We receive a download of data twice a day that has course information in it. A record can have 10 courses in it. Some of the courses need to be combined. For instance Course 1 is 12345, there are 2 other courses that are the same and therefore course 2(01234), Course 3(34566) all need to be changed to 12345. I also need to combine other course in a similar fashion, since I need to do this twice a day, ideally I would like to have a table with just columns of find and replace and use it to pick up the changes and reference it in my sql code.

An easy way to do this is the key!

Answer 1

Have you considered a cross reference table of something like

Table1
MCourse Subcourse
12345 2(01234)
12345 3(34566)

Then you can do updates like Set mainTable.Desiredfield = Table1.Mcourse where desiredfield = subcourse

Or you can create a query that uses the cross reference table to select the desired value and make a new table from that.