Simple query never returns

[mike1reynolds]

The following query never returns, even though there is an index on the name field in the Patient table:

SELECT p.Name FROM sheet4 AS s INNER JOIN Patient AS p ON p.NAME LIKE s.LASTNAME + ', ' + s.FIRSTNAME + ' %'

 

[CraigDolphin]

Perhpas you shouldn't be using reserved words (Name) as field names?

See the list on the following link for words you should avoid in naming fields in Access.

http://support.microsoft.com/kb/286335

 

[pbaldy]

I'm not sure you can use Like in the Join, though I'm not really sure. According to Jet SQL Help, that can be:

Any relational comparison operator: "=," "<," ">," "<=," ">=," or "<>."

You might try creating a query that concatenates those fields together and join against that with = to see if that works. Also, Jet likes * as a wildcard rather than %.

 

[Dennisk]

Don't forget Access uses * and not % as a wild card character.

 

[The_Doc_Man]

LIKE is going to eat your socks here. Part of your problem is that the internal template of the involved fields isn't the same.

Patient name looks like SMITH, JOHN whereas your SHEET 4 contains separate first and last names. I might create a query for your patient list that separates the last name and first name based on that comma. (look up MID$ function and INSTR$ functions.) I'm going to guess that your patient list is pretty much locked down in format. It if is not, then my best advice is to split that table up NOW so that first name and last name are separate fields. If it really is cast in stone, then you have a serious problem.

One crazy thought is to populate a table (as a temporary only) with the results of a query to do the split of the names, so that you have the patient's ID, last name, and first name as separate fields. Then JOIN the temporary table to the master patient table. Then try to join the SHEET to the JOINed table based on last name matching and some other computation on first name. You might also use this to determine if you have anomalies in the name, since you are going to go nuts trying to match up names that will never be equal.

If you DO have name mismatches (example: Robert in one table, Bobby in another), then you have a quality control issue as well as a structural issue. The only way to do this kind of search accurately is to resolve both issues.