A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

how to find records based on three sets of criteria



 
 
Thread Tools Display Modes
  #1  
Old December 2nd, 2009, 10:40 PM posted to microsoft.public.access.queries
Access Joe
external usenet poster
 
Posts: 118
Default how to find records based on three sets of criteria

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
F3100

OR

F2300
F3100
F4150

OR

F1 = yes
F3100
F4150

OR

F2300
F4150
F5100
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!
  #2  
Old December 2nd, 2009, 11:30 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default how to find records based on three sets of criteria

Try building a truth table like this --
F1T F2T F3T F4T F5T
YES 300 100
300 100 150
YES 100 150
300 150 100

Then enter criteria like this --
F1 F2 F3 F4 F5
F1T F2T F3T
F2T F3T F4T
F1T F3T F4T
F2T F4T F5T


--
Build a little, test a little.


"Access Joe" wrote:

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
F3100

OR

F2300
F3100
F4150

OR

F1 = yes
F3100
F4150

OR

F2300
F4150
F5100
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!

  #3  
Old December 2nd, 2009, 11:35 PM posted to microsoft.public.access.queries
Dorian
external usenet poster
 
Posts: 542
Default how to find records based on three sets of criteria

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".


"Access Joe" wrote:

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
F3100

OR

F2300
F3100
F4150

OR

F1 = yes
F3100
F4150

OR

F2300
F4150
F5100
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!

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:43 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.