Custom Find/Replace from excel in access (2000)

[NYIntensity]

I have a table of possible names, Column A with the accepted spelling, and columns B through ~ G are possible mis-spellings. Not all columns are full, i.e. some names may only have 3 known mis-spellings, while some may have a full 6. Next, I have an Access Database which is horribly designed, names are all in one column, and many names have 4 or 5 parts (i.e. John Michael Ray Duncan Clarke), which hopefully through programming I can convert to (Jon Mikael Rae Dunkan Clarke) (as an example).

I think to sum my goal, it would be to use the excel spreadsheet as a custom dictionary, doing a somewhat ad-hoc find and replace to correct the names that are spelled unacceptably. Can anyone offer some suggestions?

[Jezza]

Jeremy

I have had this problem for many a time. Their are 7 ways to spell Road, or so I thought until one of my foreign students proved me wrong and made it 8. I am looking at the practical answer here and not the perfect academic answer. Could you design a database with a look up table of personnel using their service number as the PK. I think you are onto a hiding for nothing trying to find alternative spellings...This is a perfect world solution but I bet you are going to tell me you have legacy data...aren't you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[NYIntensity]

What do you mean legacy data? This isn't for service members. What good would service numbers as PK do?

[Jezza]

Sorry..legacy data, I mean old data from old systems or handed to you from other sources.

Service numbers..in the UK service personnel get a unique number. I have a payroll number, just like a member of your service (may get) a personal identifier. What happens if you have 2 John Smiths, one spelt Jon Smyth the other John Smith. Can automation identify the difference between the two...the answer I would suggest is No, there has to be manual intervention and unique identifiers has to be one of the answers. Is there an another way of identifying the correct John Smith? My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth.

[NYIntensity]

Legacy data - yes.

We use SSNs in the US. I'm not worried about identifying the differences, just standardizing the names.

[jscher2000]

Did you want to correct the names "in place" in Access? Maybe a one-time process of querying for the misspelling and changing any entries found to the correct/desired spelling would work? If the data are emerging from the Access database for some other destination, other tools might be available, such as Word's AutoCorrect feature.

(Upon further review, since you would not necessarily be replacing the complete field contents in Access, and Smythe might be part of many different names, I don't know enough Access SQL to say whether you can fix the misspelled set with one clever UPDATE.)

[NYIntensity]

John,
Correcting them "in place" is fine, or I can split them, no matter there. I'm just trying to streamline the process, because there are literally thousands of possibilities - doing an update query would take quite a bit of typing <img src=/S/puke.gif border=0 alt=puke width=60 height=15>. Another thing I was thinking (but dont to how to automate) was to transpose(ish) the wrong names alongside the correct one:

Instead of
Michael - Mikael - Michel - etc

I could have
Michael - Mikael
Michael - Michel
Michael - etc

And then link my name fields to the misspelled name field, and run an update query to match the correct name field.

[HansV]

But how are you going to decide what is correct and what isn't? Some of the names you list as misspellings/variants are vailid in their own right. To name just two: Jon is short for Jonathan, and Michel is the French version of Michael.

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> BTW, JScher2000's first name is Jefferson, not John - you see how difficult it is! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

[NYIntensity]

<img src=/S/woops.gif border=0 alt=woops width=58 height=36> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> Sorry for assuming.

Hans, the most I can say about this is that it's the military, used for a very specific reason. Please don't get caught up on my dummy data, it's there just as an example of my predicament <img src=/S/groan.gif border=0 alt=groan width=16 height=15>

[StuartR]

This sounds very strange to me. I have come across many people whose names are unusual variants of common names - but these are the names on their birth certificates and they would not be pleased if we insisted that they use a standard spelling.

StuartR

[HansV]

I would create a table with two fields, one containing incorrectly spelled names, the other the corresponding correctly spelled names. For example:

<table border=1><td align=center>Wrong</td><td align=center>Correct</td><td>Johnn</td><td>John</td><td>Jonh</td><td>John</td><td>Micael</td><td>Michael</td><td>Mikel</td><td>Michael</td><td>Richart</td><td>Richard</td><td>Rihcard</td><td>Richard</td></table>
Create code that performs a double loop:
- Open a recordset on the data table and and a recordset on the above table.
- Loop through the records of the data table.
- For each record, loop through the records of the above table, edit the data record and use the Replace function to replace the incorrect name with the correct one, then update the record.

It'll be slow, but hopefully you won't have to do this frequently.

[NYIntensity]

hans, i'm bad with access vba, and terrible with excel. i'm trying to take my example table and i guess transpose it to look like your example. i can take it from there in access <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

[HansV]

The following macro will convert the table in Sheet1 in Dictionary.xls (in the attachment to the first post in this thread) to a table in Sheet2 in a format that can be imported into Access. It assumes that you already created the column headers in row 1 manually.

Sub ConvertTable()
Dim wshSource As Worksheet
Dim wshtarget As Worksheet
Dim lngSourceRow As Long
Dim lngSourceCol As Long
Dim lngMaxSourceRow As Long
Dim lngMaxSourceCol As Long
Dim lngTargetRow As Long

On Error GoTo ErrHandler

Set wshSource = Worksheets("Sheet1")
Set wshtarget = Worksheets("Sheet2")

lngMaxSourceRow = wshSource.Range("A65536").End(xlUp).Row
lngTargetRow = 1

For lngSourceRow = 2 To lngMaxSourceRow
lngMaxSourceCol = wshSource.Range("IV" & lngSourceRow).End(xlToLeft).Column
For lngSourceCol = 2 To lngMaxSourceCol
lngTargetRow = lngTargetRow + 1
wshtarget.Cells(lngTargetRow, 1) = wshSource.Cells(lngSourceRow, 1)
wshtarget.Cells(lngTargetRow, 2) = wshSource.Cells(lngSourceRow, lngSourceCol)
Next lngSourceCol
Next lngSourceRow

ExitHandler:
Set wshSource = Nothing
Set wshtarget = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub