Query to choose multiple criteria from a field

[GUIgirl]

Can a query be created that will show the results of several values in a
field. For instance. I have a field called Care&Supervision. I know this
isn't to hard but here is my problem. I have a table that has multiple Care
Facilities. Each of these over 1000 care facilities offer many care and
supervision needs. Can I have a query that can select let say all the care
facilities in Sacramento that offer care in dementia, bi-polar, combative
behavior, and males only. The Care&Supervision list of 45 care values is in
one table and the care facilities and city, state are in another table that I
have joined in relationship by the facility's license number. So in short, I
need a query that will be able to pull multiple criteria for multiple care
facilities for a particular city.

Can this be done and if so how. Thanks much
GUIgirl

 

[MGFoster]

Can a query be created that will show the results of several values in a
field. For instance. I have a field called Care&Supervision. I know this
isn't to hard but here is my problem. I have a table that has multiple Care
Facilities. Each of these over 1000 care facilities offer many care and
supervision needs. Can I have a query that can select let say all the care
facilities in Sacramento that offer care in dementia, bi-polar, combative
behavior, and males only. The Care&Supervision list of 45 care values is in
one table and the care facilities and city, state are in another table that I
have joined in relationship by the facility's license number. So in short, I
need a query that will be able to pull multiple criteria for multiple care
facilities for a particular city.

Can this be done and if so how. Thanks much
GUIgirl

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sure, like this:

SELECT F.facility_id
FROM Facilities As F INNER JOIN CareSupervision As C ON F.license_nbr =
C.license_nbr
WHERE C.care_type IN ('dementia', 'bi-polar', 'combative behavior',
'males only')
AND F.city = 'Sacramento'

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdPvBYechKqOuFEgEQJIggCffQYjXGNtdhum3leKFFiTMbbRcakAoJXo
AQXWP2ZUrch7sW5+ZA3yRxMs
=sJpx
-----END PGP SIGNATURE-----

 

[GUIgirl]

That was quick. Many thanks for your answer I appreciate it so much. I will
give it a try.

 

[KARL DEWEY]

Seems to me 'males only' would be associated with Facility instead of
care_type.
In that case the query would change like this --
SELECT F.facility_id
FROM Facilities As F INNER JOIN CareSupervision As C ON F.license_nbr =
C.license_nbr
WHERE C.care_type IN ('dementia', 'bi-polar', 'combative behavior') AND
F.Type = 'males only' AND F.city = 'Sacramento';

 

[GUIgirl]

Thanks much this is great. I really appreciate your comments.

 

[GUIgirl]

I have a table with one field with 45 conditions called CareOptionstbl. I
have a table with 15 fields and 1000 conditions called Facilitiestbl. I
also realized that I need to in some way link the conditions to the
facilities (duh) Ok now that I caught that major oversight-what is the best
way do this . I have over 1000 licensed care facilities that are sent to me
by the State of California for Sacramento County. Each facilities offers
several care options and sometimes for the larger facilities all of the 45
care options (conditions). The state does not offer what conditions (care
options) the facility just lists that it is licensed and all information
regarding licensee. My staff contacts the individual care facilities and
finds out what care options the facility offers. The care options are from
one source and facility licensing from another source and my task is to link
the two together in order to give Joe Public information on a facility to
best take care of grandmother's needs.

 

[KARL DEWEY]

You need a junction table.
Below is what I posted for someone that had buildings and furniture --

Seems to me you need three tables --
Building --
BLDG_ID - autonumber - primay key
BLDG_Name
Floor
RoomCode
Purpose

Furniture --
FurnID - autonumber - primay key
FurnType - text - chair, table, bookcase, desk, etc.
QTY - number

BLDG_Furn --
BLDG_FurnID - autonumber - primay key
FurnID - number - long integer - foreign key
BLDG_ID - number - long integer - foreign key

Set one-to-many between Building and BLDG_Furn. Also one-to-many between
Furniture and BLDG_Furn.

Use form/subform for Building to BLDG_Furn (Furniture), using a combo box to
select Furniture. Use BLDG_ID for Master/Child links.
Use separate form/subform for Furniture to BLDG_Furn (Building), using a
combo box to select Building. Use FurnID for Master/Child links.

 

[GUIgirl]

Thanks. Your awesome. This will do the trick. I will give it a try and let
you know.