Part of a Field in Query

[CJ]

I received a list of names in an Excel spreadsheet. I
imported the names into a new table into my Access DB. The
names don't match exactly; the names in the spreadsheet
don't have a middle initial etc. The entire name is in one
field in both the table and spreadsheet.

How can I run a query with a join on the name field
between the two tables and have it match on any part of
the name--like the "Match: Any Part of Field" option under
Search and Replace.

TIA
CJ

 

[JulieD]

Hi CJ

storing full names in one field in Acess isn't "good practice" ... not
really that good in excel either, what i would suggest therefore, is that
you redesign your access db & excel spreadsheet so that you don't have this
problem.

if you can't change the excel then before you import the names into access
do some conversion on them so that they are in different columns ... the
excel, data / text to columns feature is a pretty neat one to use, however
if the names aren't consistent in format (ie some have middle initials, some
have middle names & some don't have either) its a bit more complex - but not
entirely impossible.

is it possible for you to redesign the table and spreadsheet? are all the
names in the spreadsheet consisent? how often do you have to perform this
import / matching function?

cheers
JulieD

 

[Andrew W]

I agree w Julie that this a poor design -- but if you're stuck with it, you probably want to look at the LIKE operator. Pretty sure you can find that in the
expression builder.

 

[CJ]

-----Original Message-----
Hi CJ

storing full names in one field in Acess isn't "good practice" ... not
really that good in excel either, what i would suggest therefore, is that
you redesign your access db & excel spreadsheet so that you don't have this
problem.

if you can't change the excel then before you import the names into access
do some conversion on them so that they are in different columns ... the
excel, data / text to columns feature is a pretty neat one to use, however
if the names aren't consistent in format (ie some have middle initials, some
have middle names & some don't have either) its a bit more complex - but not
entirely impossible.

is it possible for you to redesign the table and spreadsheet? are all the
names in the spreadsheet consisent? how often do you have to perform this
import / matching function?

cheers
JulieD





.
Its a pretty extensive database that I inherited when I

took the job. I'd be hesitant to redesign based on not
knowing what cascade affect I would cause. I import a
couple hundred names on a weekly basis. The "match" type
operation I am attempting I do monthly. Thanks.

CJ

 

[JulieD]

Hi CJ

is the name fields the primary key?

if it isn't than the "cascade" effect would be limited to queries / forms &
reports rather than data ... which could be a pain but it might be worth it.

have you tried using the Like operator, as suggested by Andrew W, did that
do what you want?

Cheers
JulieD