MS Access Question / Multiple Find and Replace Based on Another Table

[Daniel]

I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 – Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip

[Phil Stanton]

Not too difficult to do in code, but I would hate to try using macros.

basically you will need to create 2 queries, one based on the Definitions
table, and one on the Products table

Read the first record in the Definitions table for FieldToConvert
' e.g. "Access"
Read the first record in the Products table for FieldToCheck
' e.g. "Outlook Express"
Check if FieldToCheck contains FieldToConvert ' Use the inbuilt
function Instr(FieldToCheck, FieldToConvert)
If Instr(FieldToCheck,he FieldToConvert) = 0 then it can't find the string
' Can't find "Access" in "Outlook"
so check the next record in the products table and so on to the end.
Then read the second record in the Definitions table and repeat the
procedure
If Instr(FieldToCheck,he FieldToConvert) <> 0 then it can find the string '
Can find "outlook" in "Outlook Express"
Read the ReplaceString from the Definitions table
replace the Field to check with the Replace String
update the record
back to check the next record in the Products table.

This is a loose description of what must be done, but if you can get away
from Macros and start using code you will get 1000 times more out of Access

Phil

However the problem is that having run the code to convert "access to "MS
Access",when the program is run a second time, it will again find the word
"Access" so you will end up with "MS MS Access"
"Daniel" <go***********@dmwweb.com> wrote in message
news:69**************************@posting.google.c om...

I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 - Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip

[Terry Kreft]

Try this on a copy of the tables to check it gives the desired results
UPDATE Table1, Table2 SET Table1.[Product Details] = [Table2].[Replace with]
WHERE (((Table1.[Product Details]) Like "*" & [table2].[Product Name] &
"*"));
Terry

"Daniel" <go***********@dmwweb.com> wrote in message
news:69**************************@posting.google.c om...

I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and the
data that I am pulling into it. I just have one challenge that may
require a lot of work and I wanted to find out if there was a possible
way to save time and a lot of effort on this.

I basically need to create either a query or macro that will look in
one table and basically do a mass find and replace on one particular
field based on the data in that field. I then want it to look to
another table (basically a definitions table) and replace what it
finds in the first table with the value from the definitions table. I
want to be able to do this because the field that I want to report off
of contains the product name but also more details that are useless
for this type of report.

Example:

Table1
[Product Details]
MS Word printing problem
MS Word formatting issue
MS Outlook email address book
Word 97 upgrade
Access 2002

Table2 - Definitions table
[Product Name] [Replace with]
(if found) (replace with)
word MS Word
outlook MS Outlook
access MS Access

Basically what I hope to achieve is, if the query finds Word in the
field, no matter what comes before or after it, it will replace the
entire contents of the field with MS Word. I know this can be done
manually with a find and replace or an update query, but I was
thinking that there is a way to build this definitions table and have
the query look at this table to find out what to replace the field
with depending on what it finds in the first table (Table1). I hope to
have it process about 800 records replacing the field contents with
what it finds in the definitions table. This should allow me to add
new products as time goes on as well.

The alternative that I have found for this so far, which is time
consuming and makes the database bulky, is to make 1 update query for
each product that might be listed in the table, using *word* and
updating it to "MS WORD". I then place each query in a macro and the
process runs. I have this linked to another table that has all of the
product names in the correct format (MS WORD), and use a select query
to display this on the report. Another downfall is, if new products
are added later on, they have to be entered into the table and also
must have a macro created for them. I eventually want to get this to
the point were generally end users can manage this as needed.
Any assistance or ideas on this would be greatly appreciated.

Thank you in advance.

DM266Skip