how to find records based on three sets of criteria

  • Thread starter Thread starter Access Joe
  • Start date Start date
A

Access Joe

Hey gang - Access 2003

I have a table that displays the something similar to the following:

PatientID____F1____F2____F3____F4____F5
A101_______yes___392___45____121___300
A102_______no____312___57____324___100
A103_______yes___203___213___242___192

What I want to do is a query to find those patients who meet THREE specific
sets of criteria in combination. Sounds easy enough. But here's the
catch...that criteria could be all over the place. For example, criteria
could be any combination of the following (and these aren't all of the
possible combinations, just a handful):

F1 = yes
F2 >300
F3<100

OR

F2>300
F3<100
F4>150

OR

F1 = yes
F3<100
F4>150

OR

F2>300
F4>150
F5<100
ETC. ETC. ETC

So while each field will always have the same conditional test, the patient
needs to meet at least THREE of those parameters (out of five fields total)
to get displayed. As an example, patient A102 does NOT meet the criteria for
F1, but DOES meet the criteria for F2, 3, & 4. So that's someone I'd like to
see.

So is there any way to find patients who meet at least THREE sets of
criteria in a table where they've got five columns to look through? This is
a tough one, and I'm not feeling very hopeful about it given all the possible
combinations. But any assistance would be appreciated! (if any coding or SQL
is necessary, please tell me where to go / what to click on to begin entering
that code). THANK YOU!
 
Where does your criteria for the search come from?
Is it entered via a form and will you be doing a single search at a time or
multiple searches?
There is nothing impossible about what you want but more information is
needed.
You sould be able to just build all your OR tests into a query in SQL mode.
SELECT... FROM MyTable WHERE (conditions) OR (conditons) OR (conditions)
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Back
Top