How to query the different format of the same name

[JoJo S via AccessMonster.com]

Please help me find a better way to query. I have a field that contains
physician full name in various format. For example, it can be written as Soko
M.D. Mary J or Soko MD,Mary or Soko,Mary, etc. I can retrieve data by putting
in LIKE SOKO*MARY* in query design. Now, I don't want users to get in query
design. What would be the best way for my users to type in just SOKO,MARY
from a form and be able to retrieve all the different name format related to
this doctor? In addition, there could be more than 1 physician to query at a
time.
(condition: I cannot make the source table separate into last name and first
name. ).

Thank you very much!!

 

[John Vinson]

Please help me find a better way to query. I have a field that contains
physician full name in various format. For example, it can be written as Soko
M.D. Mary J or Soko MD,Mary or Soko,Mary, etc. I can retrieve data by putting
in LIKE SOKO*MARY* in query design. Now, I don't want users to get in query
design. What would be the best way for my users to type in just SOKO,MARY
from a form and be able to retrieve all the different name format related to
this doctor? In addition, there could be more than 1 physician to query at a
time.
(condition: I cannot make the source table separate into last name and first
name. ).

Pity... because that's your best option!

Ok, let's try this:

WHERE [Physician Name] LIKE "*" & Trim(Left([Enter name:],
InStr([Enter name:], ",") - 1) & "*"
AND [Physician Name] LIKE "*" & Trim(Mid([Enter name:], InStr([Enter
name:], ",") + 1) & "*"

This assumes that your users are consistant at entering a name with a
comma; and it will also find both Dr. Jane Robert and Dr. Robert Jane
if the user enters "Robert,Jane".

John W. Vinson[MVP]

 

[JoJo S via AccessMonster.com]

John, Thanks you very much. But I cannot get it to work via your method and I
don't know where it went wrong. Can you help me again? Here is my table and
qry:

tblPhysicians
de Physician name
30 MARDY M.D.,JOHN L [RPL]
30 MARDY M.D.,JOHN L
30 MARDY MD,JOHN L

Qry:

PARAMETERS [enter name:] Text ( 255 );
SELECT TblPhysicians.de, TblPhysicians.[Physician name]
FROM TblPhysicians
WHERE ((([a].[physician name]) Like "*" & Trim(([a].[physician name])=Left(
[Enter name:],InStr([Enter name:],",")-1) & "*" And ([a].[physician name])
Like "*" & Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

I entered MARDY,JOHN and nothing returned. (There never is a space between
last and first name; only a semi-colon in my source data)

What if I need to enter multiple names at the same time to run the query?
Create a form?

Thank you very much!!!!

Please help me find a better way to query. I have a field that contains
physician full name in various format. For example, it can be written as Soko

[quoted text clipped - 6 lines]

(condition: I cannot make the source table separate into last name and first
name. ).

Pity... because that's your best option!

Ok, let's try this:

WHERE [Physician Name] LIKE "*" & Trim(Left([Enter name:],
InStr([Enter name:], ",") - 1) & "*"
AND [Physician Name] LIKE "*" & Trim(Mid([Enter name:], InStr([Enter
name:], ",") + 1) & "*"

This assumes that your users are consistant at entering a name with a
comma; and it will also find both Dr. Jane Robert and Dr. Robert Jane
if the user enters "Robert,Jane".

John W. Vinson[MVP]

 

[John Spencer (MVP)]

Pardon me for jumping in. If you are going to alias the table then you must do
so consistently.

PARAMETERS [enter name:] Text ( 255 );
SELECT A.de, TblPhysicians.[Physician name]
FROM TblPhysicians as A
WHERE [A].[physician name] Like "*" & Trim(Left([Enter name:],InStr([Enter
name:],",")-1)) & "*" And
[A].[physician name] Like "*" & Trim(Mid ([Enter name:],InStr([Enter
name:],",")+1)) & "*"

John, Thanks you very much. But I cannot get it to work via your method and I
don't know where it went wrong. Can you help me again? Here is my table and
qry:

tblPhysicians
de Physician name
30 MARDY M.D.,JOHN L [RPL]
30 MARDY M.D.,JOHN L
30 MARDY MD,JOHN L

Qry:

PARAMETERS [enter name:] Text ( 255 );
SELECT TblPhysicians.de, TblPhysicians.[Physician name]
FROM TblPhysicians
WHERE ((([a].[physician name]) Like "*" & Trim(([a].[physician name])=Left(
[Enter name:],InStr([Enter name:],",")-1) & "*" And ([a].[physician name])
Like "*" & Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

I entered MARDY,JOHN and nothing returned. (There never is a space between
last and first name; only a semi-colon in my source data)

What if I need to enter multiple names at the same time to run the query?
Create a form?

Thank you very much!!!!

Please help me find a better way to query. I have a field that contains
physician full name in various format. For example, it can be written as Soko

[quoted text clipped - 6 lines]

(condition: I cannot make the source table separate into last name and first
name. ).

Pity... because that's your best option!

Ok, let's try this:

WHERE [Physician Name] LIKE "*" & Trim(Left([Enter name:],
InStr([Enter name:], ",") - 1) & "*"
AND [Physician Name] LIKE "*" & Trim(Mid([Enter name:], InStr([Enter
name:], ",") + 1) & "*"

This assumes that your users are consistant at entering a name with a
comma; and it will also find both Dr. Jane Robert and Dr. Robert Jane
if the user enters "Robert,Jane".

John W. Vinson[MVP]

 

[John Vinson]

John, Thanks you very much. But I cannot get it to work via your method and I
don't know where it went wrong. Can you help me again? Here is my table and
qry:

tblPhysicians
de Physician name
30 MARDY M.D.,JOHN L [RPL]
30 MARDY M.D.,JOHN L
30 MARDY MD,JOHN L

Qry:

PARAMETERS [enter name:] Text ( 255 );
SELECT TblPhysicians.de, TblPhysicians.[Physician name]
FROM TblPhysicians
WHERE ((([a].[physician name]) Like "*" & Trim(([a].[physician name])=Left(
[Enter name:],InStr([Enter name:],",")-1) & "*" And ([a].[physician name])
Like "*" & Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

Well... that is NOT what I suggested.

You're taking the LOGICAL EXPRESSION

Trim([a].[physician name]) = Left(...

which will return either True (-1) or False (0) and searching for that
numeric value within the physician name.

Try what I *did* suggest: paraphrased in your terms,

PARAMETERS [enter name:] Text ( 255 );
SELECT TblPhysicians.de, TblPhysicians.[Physician name]
FROM TblPhysicians
WHERE ((([a].[physician name]) Like "*" &
Trim(Left([Enter name:],InStr([Enter name:],",")-1)) & "*"
And ([a].[physician name]) Like "*" &
Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

I entered MARDY,JOHN and nothing returned. (There never is a space between
last and first name; only a semi-colon in my source data)

You might want to put the Trim() expressions in the query as
calculated fields, just for debugging purposes - they should return
MANDY and JOHN respectively.

What if I need to enter multiple names at the same time to run the query?
Create a form?

Yes; and write VBA code to construct a really complex SQL statement.
Not fun.

Is there ANY chance that you could give the user a Combo Box listing
the physician names, sorted alphabetically, so they can just pick a
name, rather than hazarding a guess at the correct spelling, and the
overhead of this complex search?

John W. Vinson[MVP]

 

[JoJo S via AccessMonster.com]

Grateful to both of you, John Vinson and John Spencer. Now it works!!

I’d love to make a combo box of listing of the physician names ONLY IF it can
function as following: If “MARDY MD,JOHN” is picked from the list, all other
name formats of this doctor will also be included as criteria in querying,
such as Mardy M.D. JOHN, MARDY M.D. JOHN L. The physician name can be
entered in any format so I cannot possibly have a definite list of names to
include in combo box list. What kind of coding will be required?

Many many thanks!!

John, Thanks you very much. But I cannot get it to work via your method and I
don't know where it went wrong. Can you help me again? Here is my table and

[quoted text clipped - 14 lines]

[Enter name:],InStr([Enter name:],",")-1) & "*" And ([a].[physician name])
Like "*" & Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

Well... that is NOT what I suggested.

You're taking the LOGICAL EXPRESSION

Trim([a].[physician name]) = Left(...

which will return either True (-1) or False (0) and searching for that
numeric value within the physician name.

Try what I *did* suggest: paraphrased in your terms,

PARAMETERS [enter name:] Text ( 255 );
SELECT TblPhysicians.de, TblPhysicians.[Physician name]
FROM TblPhysicians
WHERE ((([a].[physician name]) Like "*" &
Trim(Left([Enter name:],InStr([Enter name:],",")-1)) & "*"
And ([a].[physician name]) Like "*" &
Trim(Mid([Enter name:],InStr([Enter name:],",")+1) & "*"))));

I entered MARDY,JOHN and nothing returned. (There never is a space between
last and first name; only a semi-colon in my source data)

You might want to put the Trim() expressions in the query as
calculated fields, just for debugging purposes - they should return
MANDY and JOHN respectively.

What if I need to enter multiple names at the same time to run the query?
Create a form?

Yes; and write VBA code to construct a really complex SQL statement.
Not fun.

Is there ANY chance that you could give the user a Combo Box listing
the physician names, sorted alphabetically, so they can just pick a
name, rather than hazarding a guess at the correct spelling, and the
overhead of this complex search?

John W. Vinson[MVP]

 

[John Vinson]

Grateful to both of you, John Vinson and John Spencer. Now it works!!

I’d love to make a combo box of listing of the physician names ONLY IF it can
function as following: If “MARDY MD,JOHN” is picked from the list, all other
name formats of this doctor will also be included as criteria in querying,
such as Mardy M.D. JOHN, MARDY M.D. JOHN L. The physician name can be
entered in any format so I cannot possibly have a definite list of names to
include in combo box list. What kind of coding will be required?

Garbage in, garbage out.

Your name file is... I'm sorry to say... garbage, and thereby not
reliably searchable or maintainable. Human intellect coding is your
best bet; it's probably beyond the power of computers!

The best I can suggest is a combo box based on a SELECT DISTINCT query
based on the table itself, sorted alphabetically; this would at least
put all the variants of Dr. Mardy together in one place, though you
will also get things like

MARDY D.O. JOHN
MARDY D.O. KAREN
MARDY M.D. JOHN
MARDY M.D. JOHN L.

because your table violates normal form by storing different
attributes (firstname, lastname, title, middle name) jammed
higgldy-piggledy in a single field.


John W. Vinson[MVP]