Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Matching part of a field in a query (2003)

    I have a table called tblCurrentCourses which has a field called CInstructor. This information is dumped from our information system at the university and the instructor names are given in an inconsistent fashion. For instance, some of them will have lastname, first initial; some will only have the last name, etc. I have another table called Professor Mailing List which is very neat and tidy and has the Title of the professor (Dr., Professor, etc.), LastName, FirstName, Campus Department, Room Number. I want to take the information dumped into CInstructor, compare it to Professor Mailing List, and put the neat data into two fields in tblCurrentCourses called CInstLN and CInstFN. The trouble I am having with an update query is that the CInstructor field does not match exactly the LastName field in the table Professor Mailing List. I have tried using Like, DLookup and Instr to remove the last name from CInstructor. None of them seem to work because I have nothing to use to connect the two tables.

    Is what I'm asking impossible? <img src=/S/woops.gif border=0 alt=woops width=58 height=36>

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

    Re: Matching part of a field in a query (2003)

    An update query would be risky to run, unless you found a way to ensure that there wouldn't be double or false matches. Check carefully and make a backup copy before you do so.

    You can create a query based on the tables, without joining them. Add the Cinstructor, CInstLN, CInstFN fields, LastName and FirstName fields. Create a calculated column

    InStr([CInstructor],[LastName])

    Set the criteria for this column to >0 and clear the Show check box. Switch to datasheet view to see how the records are matched. If you're satisfied, switch back to design view and change the query to an update query. Enter [LastName] in the Update to row for CInstLN and [FirstName] in the Update to row for CInstFN.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> It would be nice if you provided some feedback to the replies you receive - that way other Loungers know whether they were helpful or not. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Michigan City, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Matching part of a field in a query (2003)

    Thank you so much!

    This worked perfectly!

    You are such a gem, Hans!

    Jodi

Posting Permissions

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