Rename field values with values from another table

[Tskutnik]

I have two tables.descriptions I'd like to relate and use to find/replace in bulk.
[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)

I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".

Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico

[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico

Can someone tell me the SQL for this? Thanks

 

[plog]

Can someone tell me the SQL for this?

No, because this involves more than SQL. Using VBA will make your life a little easier, but at some point its going to take a person to verify matches have been made.

Computers don't have opinions, that can't see that "Geic" is close enough to "Geico" to be a match. However, you can program a computer with specific instructions that tell it that a match occurs if the first 4 characters match between them. But that's just one case. What if the "i" and "e" are transposed? To make that match requires another rule. "geico" and "Geico" aren't exact matches either, so that's another rule. Wait, its worse: "Cheeseburger" and "Cheetos" aren't matches, but if you use that first 4 characters rule they would be.

It's a dance between false positives (identified matches that really aren't) and false negatives (not identified as matches but really are). Which ultimately means a human has to intervene somehow.

My advice is to write your rules of what defines a match. Then build a function in VBA that takes 2 strings, implements your rules and tells you if they are a match. Once you have that you can build a query to process those matches, review them with your own eyes to make sure they are in fact correct, make any manual changes you find and then finally run an UPDATE query to make them.

 

[Tskutnik]

Plog - Thanks. The tests would certainly be conducted; I just need the code to get started.
SQL or Macros either way I’m fine – I just need to know what to do.
Let me explain better...

The [Rename] table has two fields [Description] and [NewDescription], both entered by the user. In my example one record would be something like:
[Rename].[Description] = "Geico" &"*"
[Rename].[NewDescription] = "Geico"

The [Rename].[Description] value is used to match anything that starts with “Geico” from the [Checking].[Description] records.
e.g. if [Checking].[Description] = Geico12345 would find/match [Rename].[Description] = "Geico" &"*"

Once the match is found the code should replace the [Checking].[Description] = Geico12345 value with the [Rename].[NewDescription] = "Geico".

Examples of a bulk change would be
[Checking].[Description] = "Geico Limited 2/21/15"
[Checking].[Description] = "Geico Limited 2/18/15"
[Checking].[Description] = "Geico LTD 2/15/15"

[Rename].[Description] = "Geico L"
(with the code adding any needed "&"*" to capture all records that begin with this string)
[Rename].[NewDescription] = "Geico Limited"

Result: All three [Checking].[Description] records would be changed to "Geico Limited"

 

[Tskutnik]

I have the following code which partially works

UPDATE checking, rename SET [Checking].[Description] = [Rename].[newdescription]
WHERE (((Checking.[Description]) like "*" & [Rename].[sourceDescription] &"*") );
Problem is the original [Checking].[Description] value is not deleted in full.

e.g. "Geico12345 54321" might be changed to "Geico 54321" with some of the original string remaining.

 

[plog]

This is a different issue than you initially posted. It has its own problems.

I'm unclear what Rename.Descriptions holds. You say it could have this value:

"Geico" & "*"

Which to me means it contains 13 characters (5 letters, 4 quote marks, 2 spaces, 1 asterisk and 1 ampersand). You later say that another record could have this in it:

"Geico L"

But then you say the code should see that and add an add an ampersand and quote marks and an asterisk to it

I don't know if that field truly contains all those characters, my guess is you did a horrible job posting what your fields actually could contain. But my main point is, I'm having trouble parsing it. And this issue you have is all about parsing now. You are going to have to write a function that reads from Rename, parses the data in Description, constructs an SQL UPDATE query based on that and then execute it.

Also, the example you gave is not possible--you will never end up with a "Geico Limited" record. The "Geico*" rule will always supersede the "Geico L*" rule. (Because the "Geico*" rule will convert it to "Geico").

Lastly, you're gonna need more than 2 fields because order now matters. You could have some rules that change the Checking.Description field to a new value that hits a different rule. So depending on the order they get run it could effect the final result of that field.