Queries with information seperated by a comma

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database from an Excel spreadsheet. On the spreadsheet,
there is a feild for "language". The responses have been entered as the
first letter or first two letters of the language, e.g Spanish is "S",
English is "E", and Chinese Mandarin is "CM".
When imported into Access, the language of someone speaking Spanish,
English, Manadarin would read "S,E,CM" in the lnaguage column.

With data organized like this, is it possible to run a query on one
particular language. I have tried to do this and Access only seems to
recognize the first letter of the field. So if I wanted to run a query for
Manadarin, Access is only giving me the people that speak Manadarin and not
those who speak Manadarin and another language.

Is there a work aorund without having to do a "yes or no" for each language?

Joe
 
Yes, maybe.
Criteria like this should do it ---
Like "*" & [Enter language] & "*"

This will work unless in addition to "S" you have "SI" for Southern India or
"ES" for Eastern Sangallie.

You could have a one-to-many related tables between people and their
language ability.
 
You could always use the following

Field: WithComma: ", " &[Languages] & ","
Criteria: Like "*, " & [Enter language] & ",*"

Of course, that fails if someone enters a space before the comma or fails to
enter exactly one space after the comma.

The bottom line is add a table to hold the languages spoken by the
individuals. Your searches will be much more accurate.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

KARL DEWEY said:
Yes, maybe.
Criteria like this should do it ---
Like "*" & [Enter language] & "*"

This will work unless in addition to "S" you have "SI" for Southern India
or
"ES" for Eastern Sangallie.

You could have a one-to-many related tables between people and their
language ability.

stumped said:
I am creating a database from an Excel spreadsheet. On the spreadsheet,
there is a feild for "language". The responses have been entered as the
first letter or first two letters of the language, e.g Spanish is "S",
English is "E", and Chinese Mandarin is "CM".
When imported into Access, the language of someone speaking Spanish,
English, Manadarin would read "S,E,CM" in the lnaguage column.

With data organized like this, is it possible to run a query on one
particular language. I have tried to do this and Access only seems to
recognize the first letter of the field. So if I wanted to run a query
for
Manadarin, Access is only giving me the people that speak Manadarin and
not
those who speak Manadarin and another language.

Is there a work aorund without having to do a "yes or no" for each
language?

Joe
Click to expand...
Click to expand...
 
Back
Top