Queries with information seperated by a comma

[stumped]

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

 

[Jeff Boyce]

Joe

Access is not a spreadsheet. To get the best use of Access' features and
functions, you'll need to spend some time studying up on "normalization".
The simple explanation is that you can do a lot more with Access if your
data isn't just a copy of what was in Excel.

While there is a way to extract out those records that have an "E" in that
[Language] field, putting multiple facts into a single field ("S,E,CM") is
not a good relational database design. If one person can have one or more
languages, in Access (and other relational databases) this would be
portrayed using three tables.

One table holds the person information (firstname, lastname, DOB). Another
table holds the possible languages. A third table holds the valid
combinations of person and language. For your example, that person would
show up in three records/rows in the third table. Well, actually, you
wouldn't need or want to put all the person info in that third table, just
the PersonID from the first table.

With a design like this in Access, finding all the "E"s is a simple query
against that third table.

Now, back to your situation...
If you create a new query in design view, add YOUR table, add the [Language]
field, and put the following:
Like *E*
in the criterion under that field, Access will return all rows that have an
"E" anywhere in that field.

This is how you could do it with what you described... and I recommend
against it! Take another look at the alternative I outlined above.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP