Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom Find/Replace from excel in access (2000)

    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?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Find/Replace from excel in access (2000)

    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>
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Find/Replace from excel in access (2000)

    What do you mean legacy data? This isn't for service members. What good would service numbers as PK do?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Custom Find/Replace from excel in access (2000)

    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.
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Find/Replace from excel in access (2000)

    Legacy data - yes.

    We use SSNs in the US. I'm not worried about identifying the differences, just standardizing the names.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Custom Find/Replace from excel in access (2000

    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.)

  7. #7
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Find/Replace from excel in access (2000

    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.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Find/Replace from excel in access (2000

    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>

  9. #9
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Find/Replace from excel in access (2000

    <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>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  10. #10
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Custom Find/Replace from excel in access (2000

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Find/Replace from excel in access (2000

    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.

  12. #12
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom Find/Replace from excel in access (2000

    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>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom Find/Replace from excel in access (2000

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •