Matching part of a field in a query (2003)

Topic

New Reply

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?

Reply | Quote

Replies

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.

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.

Reply | Quote

Thank you so much!

This worked perfectly!

You are such a gem, Hans!

Jodi

Reply | Quote