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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|