Match similar values

[enjiel]

Here's a fun one, I've read a number of forums that describe this very situation and the solutions are case by case. This one in particular, I managed to get to work but for some reason it stopped working after restarting Access.
Background: Unfortunately, the data that I need to use here is done by a different office, in a galaxy far far away and, despite our complaints and profesional emails the Member and Supervisor values are filled using different formats. I've been trying to write a statement that will identify the supervisees of each member. End Background:
The Member format is: Last, First [sometimes a middle initial, sometimes full middle name]
The Supervisor format is: Last First (some supervisors are left null and in some cases a number... it's pretty messy).
Rather than fixing each value by hand, I've thought of two methods. I could use an SQL statement to make up for the difference, or I could write a VBA script to fix all of the cells when I update the database from the spreadsheet that we recieve. (Or we could continue to do it line by line but the process is time consuming).
So far, I've managed this SQL statement:
Using Table MEMBER in a recursive join to MEMBER_1 and columns FULL_NAME (for each member) and SUPV_NAME (for the supervisor).
SELECT MEMBER.SUPV_NAME, MEMBER.FULL_NAME
FROM MEMBER INNER JOIN MEMBER AS MEMBER_1 ON LEFT(MEMBER.SUPV_NAME,(InStr(Nz(MEMBER.SUPV_NAME," 0"), ' ')-1)) = LEFT(MEMBER_1.FULL_NAME,(InStr(MEMBER_1.FULL_NAME, ' ')-2));
This finds the text until a space for SUPV_NAME (which has no comma) then does the same for FULL_NAME (which has a comma, hence -2) then compares them. Also, I use Nz because Supervisor is sometimes null (even though it shouldn't be). The statement LEFT(MEMBER.SUPV_NAME,(InStr(Nz(MEMBER.SUPV_NAME," 0"),' ')-1)) works as a SELECT statement, the probelm comes up when I try to compare it to MEMBER_1.FULL_NAME in the JOIN. I get this error: "Invalid Procedure Call".
So close, and yet so far. Does anyone have any ideas on how I could get this to behave properly? As it stands, if this did work, it would only compare last names, I figured I'd work on getting it compare first names as well if I can get this far. Otherwise, I'm not really sure what's causing the error.

[rpeare]

your post is.... not exactly the greatest!

How about you give an example of your INCOMING data and what you WANT it to be.


From the sound of it though you are strictly doing a person name (text) comparison which is *always* going to be a problem, for instance, what if you have a John A Smith and a John B smith but on one of them the middle initial is left out, you'd have to know that the other John Smith is somewhere in your dataset with the correct middle initial so you could 'deduce' the correct middle initial for the one without the middle initial, same thing goes for any first name.

[enjiel]

Sorry about the format, I’ll see if I can clean that up.

This query is a best effort solution for a system that I can’t seem to fix. (I’ve been sending emails but it’s not likely to ever happen). So there will most definitely be issues when more than one people have the same name, but that’s a minor inconvenience for a solution to a larger problem. There’s simply too much data that we have to sift through by hand.

The table is set up as such:
EmployeeID/ EmployeeFull_Name/ RaterFull_Name/ ect.ect.ect.

If I could change the RaterFull_Name to RaterEmployeeID then the solution would be simple, but any complaints fall into the void since no one else is going to take the time to update as much information as would have to be updated to make that happen.

The output that I’m looking for is a list of all the members who’s rater is (RaterFull_Name).

[rpeare]

No example data or example of what you want the final product to be?

[enjiel]

I'm sorry, I misunderstood what you were asking. Here's an example:

EmpID/ FULL_NAME/ RaterFull_Name
39DJ2/ White, Barry J/ Jackman Hough
283EJ/ Scottsman, Scott S/ Dean James
JEI49/ Doe, John D/ Dean James
NB31E/ Dean, James R / Boucher Bobby
098JJ/ Boucher, Bobby B/ Jackman Hough

If I select "Dean, James R" from the form control, then it would return "Scottsman, Scott S" and "Doe, John D".
The "Full_Name" field has a comma and a middle initial. The "RaterFull_Name" does not have a comma or a middle initial. (an unfortunate format and I don't know why they do it this way)

[rpeare]

so are you looking to split up the names into first/last

create a form
on that form create a combo box

make the ROW SOURCE of that combo box:

SELECT RaterFull_Name FROM <TableName> GROUP BY RaterFull_Name ORDER BY RaterFull_Name

name the combo box cboTest
save the form as frmTest

Create a query

SELECT * FROM <TableName> WHERE (RaterFull_Name) = [forms]![frmTest]![cboTest] ORDER BY Full_Name

Save the query with any name you want.

[enjiel]

Sorry it took me so long to get back to you. This worked perfectly. Thank you!