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
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!
0
Utf
12/2/2009 9:40:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
839 Views

Similar Articles

[PageSpeed] 51

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
> 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!
0
Utf
12/2/2009 10:30:01 PM
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
> 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!
0
Utf
12/2/2009 10:35:01 PM
Reply:

Similar Artilces:

Locate a cell, based on a criteria, then use the 'Cell' command...
I am using a pivot table to configure some data. In the first column, the pivot table of course displays only one instance of the text representing of data group. What I need to do is determine in which cell a particular item of text (i.e. the criteria) resides in. Once I can locate the cell where my 'criteria' text string resides, I can then use the CELL function to return the row number. My question is... what excel formula can I use to locate a cell in a single column range, where the cell contains the text = "XYZ" My eventual plan is to then use the following ...

Assigning Records
When a Microsoft CRM Record is assigned from one Microsoft CRM User to another, the Microsoft CRM User who assigned the record will maintain share privileges on that record once it has been assigned. The ownership of that record will be changed to the Microsoft CRM User the record was assigned to, but the User who assigned the record (the previous owner) will still maintain share privileges on that record. (KB861702) We have a situation where we our system reassigns accounts to different users quite often. We would not like the previous owner to have share privileges on that record. It...

Outlook 2003 cannot find my files
I am running Windows XP Professional Tablet. I upgraded to Office 2003, and now I cannot see my contacts and my calendar is empty. I do not know what to do now, as I cannot run the earlier version of Outlook. I can find the pst files, but I cannot figure out how to import them into this new version. In addition, I cannot see the local email files that I have stored in my directory. Help! Sincerely, GVG Don't try to import - use File->open->outlook data file and browse to the location of your .pst file. --� Milly Staples [MVP - Outlook] Post all replies to the group to ke...

Printer setting on sales reports
I am trying to setup a different default printer on a specific sales transaction, is that possible? I am going to Sales->Sales Batches, and for one of the batch transactions, I want them to print to a different printer. I know I can choose another printer manually once I am there by going to print setup, but would it be possible for me to eliminate that step for my users? My appoligies if I am using incorrect terminoligy for some of these things, I am not too familiar with Great Plains yet. Using Named Printers, you can direct invoices to one printer and picking tickets to another. ...

Setting up multiple accounts in Wndows live mail
I set up two accounts and I received messages from both accounts in to the default account. What do I need to correct? Do you mean that messages for both accounts are populating the same Inbox folder? If so, create another folder called Inbox2, for example, and then create a rule to move one of the account's messages in to it. "Bob" <Bob@discussions.microsoft.com> wrote in message news:4EE09688-A477-4AE8-BA74-5B7104BF951C@microsoft.com... > I set up two accounts and I received messages from both accounts in to the > default account. > What do I n...

Smart List search criteria
It would be helpful if 'ends with' was added as an operator in SmartList's search criteria. ...

How to set new default for plot area settings
Hi, In Excel 2003, how do I set new default value for plot area to Automatic? Now the default is custom, and background colour one that I do not want. I can change the plot area to automatic for a chart I have created, but would like to change the default value so, that whenever creating a new chart, it would have Plot area settings "Border" and "Area" set as automatic. Obviously it should be done within the template Book.xlt, but how? Hi, You can format a chart and store it as a user defined chart type, which can also be set as the default. http://pubs.logicalexp...

DPM 2007 and DVD recorders
I'm going to evaluate DPM 2007 but before I've a question: does DPM 2007 supports DVD recorders for permanent data archive ? Thanks in advance. F. On 22 Feb, 20:25, "Fab <fabrizio.nos...@nospam.nospam>" > I'm going to evaluate DPM 2007 but before I've a question: does DPM > 2007 supports DVD recorders for permanent data archive ? From the technet web site: "DPM does not support Write-Once Read-Many (WORM) media." Hope to be wrong... F. http://www.ss-infrastructure.com/2009/12/dpm-and-tapes-things-to-consider.html ...

need excel worksheet help, running total based on date
I am trying to add a series of numbers in different worksheets(same spreadsheet) based upon the date. I don't want the future numbers in the total until the date they are associated with is passed. i.e. in A1 of each worksheet is 200. Each worksheet is titled by date (Mar 5, Mar 19, etc) I want the running total to reflect the amount to the current date. If it's March 18th, the totals would be only thru the worksheet of Mar 5. Any help is appreciated. My solution needs 2 cells, one of which may already be in use: You need a cell on each of the dated sheets that...

Item Extension record required for all items in Returns Management
We've been testing the Returns Management module using the Fabrikam company to see if it will be something we can benefit from. Our testing has shown that in order for the system to pull the item's Current Cost (from the Item Master), you must create an Item Extension record for the item and check the box marked 'Use Current Cost'. If you don't create the record, the system will default to a zero cost. (This assumes that you don't associate the return with an existing invoice where the customer bought the item). To me, this seems to be a maintenance nightmare. O...

how to set audio language
hi, my WMP version is 11.0.5721.5145. i was trying to watch some japanese anime (OGM files) on WMP but it came out in it original japanese audio and the english voiceover. how do i shut one of the audio off so i only hear one voice? WMP has never handled multiple audio tracks in a video unfortunately. Up to ver. 11 it superimposed them as you've found, in ver. 12 it seems it just plays the 1st track encoded and ignores the other. You will need to use a different player such as Media Player Classic, VLC or Winamp which allows audio track selection. "Sue" <...

How do I save the Page Setup settings for letterhead usage?
I have new company letterhead and have worked hard to set top, left, bottom and right margins. How do I save those in Word 2003 so that if I'm printing on this letterhead in future, I can just call up the format and create the document without typing in all the measurements? Save it as a template; see http://word.mvps.org/FAQs/Customization/CreateATemplatePart1.htm -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "NowWow" <NowWow@discussions.microsoft.com> wrote in message news:0D892886-B419-4784-...

Locking records once "closed"
The user is selects their name from a drop down menu. When the cell e X1 is filled the completed date is automaticallly entered based o if(X1<>"",NOW(),""). After this happens I would like to change the formula cell to a date so that it will not change everytime the worksheet is opened. How do achieve this without using VB. Is it possible to protect cells once X1<>"", or better yet change al dates on that row to lock. Or is there another method of achieving the same objective. That i once a record is complete no further changes cand be made. T...

Double click to open form and create new record
Hi team, Have scoured all of the posts and tried several different things for days but just can't seem to nut this one out. I have a form (FrmSeedBankStockAvailable) in datasheet view that lists seed lots by BatchID. I want to be able to double click on the record selector for a particular batch and have a form (DialogSeedBankWithdrawal) open so that I can make a new withdrawal entry, with the BatchID already entered from the selected record. I know that to do this I need to use the forms' DblClick property. I have been using the following code (and tried variations of from...

Copying record value from column in multiple sheets into single sh
Hi, I have some employee Records in sheets "Nov07","Dec07" and "Jan08". the first two columns(A and B) are employee number and name in each sheet, and column C is their overtime. I want to create a consolidated sheet "YTD" so that I can see all their YTD overtime in one sheet and do some analysis. Hence the new sheet will have first two columns A and B same as above howver column C, D and E will be the overtime from each sheet. It would have been a simple copy paste if the number of employee had been static, but it changes every months as new em...

setting up my fax
H How do I set up fax on outlook or wndows messaging to access using my phone line (not internet dial-up)? I cannot send nor receive as no fax option is shown on menu of outlook and help does not explain set up Please help. OS = Win9 office 2000 premiu modem= duxbury (class1 thank Jea You must first provide the mail support mode of Outlook 2000 you are using (Line 2 of Help | About if you don't know: Internet Mail Only VS Corp/Workgroup). -- Russ Valentine [MVP-Outlook] "jean" <anonymous@discussions.microsoft.com> wrote in message news:5D1E4A24-530F-4A46-A968-2C79...

Setting Hotmail as Default Folder
Is there a way to make my Hotmail folder the default folder? As it is now, after Outlook opens I have to click on the Hotmail folder to get to my Hotmail Inbox. I would also like to use all the features that are in the normal Outlook folder, e.g., Calendar, Contacts, etc. What version of Outlook???? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the SWEN virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, DaveP asked: | Is there a way to make my Hotmai...

2 sets of data I need one line
I have two sets of data, height and distance and I want both to be on the same line and not two seperate lines. I would appreciate any help Thanks Hi, You need to create a xy scatter chart. Assuming you have already tried selecting both ranges of data and creating a chart try this instead. Select the y values and create a xy scatter chart. Then right click chart and use the Source data dialog to specify the x values. Cheers Andy Tim wrote: > I have two sets of data, height and distance and I want both to be on the > same line and not two seperate lines. I would appreciate any he...

ExecuteXmlReader w/ Multiple result sets from SP
I have an sp that outputs multiple xml fragments w/ no root. The sp calls individual sp's to output the correct set of data and each "type" has different fields. ex.: <LEADERBOARD @Type='Rushing'> <LEADER @Carries=''/> </LEADERBOARD> <LEADERBOARD @Type='QBRating'> <LEADER @Rating='' @CompletionPct=''/> </LEADERBOARD> <LEADERBOARD @Type='Tackles'> <LEADER @Assisted='' @Tackles='' @Total=''/> </LEADERBOARD> I need to take the output and use XSL...

Sorting a Report Based on Criteria
I have a report that I would like to sort based on user-entered criteria. There are two option groups in my form. Each option group has four fields (Name, Title, Date, ID) that the user can search. The user would select one option from each group. The first group is the primary sort and the second option group is for the secondary sort. For example, if the user selects Name from the Primary option group and Date from the Secondary option group, the report should be sorted by Name and then by Date. What code can I use to enable the user to search this way? Use a calculated field ...

Retrieving data from multiple records
How do I put data from multiple records in one report on one line. I have parents in one table and children in another. Each child has its own record. How can I include a sentence like this in a report when the children's names come from various records: "Thank you for registering Brianna, Sean, and Emma for our art workshop." Also, there are other children in the family who were not registered. Using Access 2003. Thanks for your help. There is a generic function that will concatenate the child records at http://www.rogersaccesslibrary.com/forum/forum_posts.asp?T...

Get the ID of a newly added record
I have a table that is being accessed through a class derived from MFC's CRecordSet. The table is actually implemented in Access and it's ID is an auto number field. After doing an AddNew to the table, how can the newly added row's ID be retrieved? In SQL server, I just would have created a stored procedure which would have added the row and returned the ID. obviously, I can't do that in Access. Any ideas? -- Richard Lewis Haggard Hi U�ytkownik "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> napisa� w wiadomo�ci news:%23VlM$X1%23EHA.4092@TK2MSF...

finding average within a range
Is there a way to sum a group of numbers based on whether it falls within a specific. For example. Suppose I want to find the average of the numbers between 7/4 and 7/8 from the following data A B 7/3 4 7/4 8 7/5 7 7/6 9 7/7 8 7/8 8 7/9 3 i want the average of the 8,7,9,8,8 and exclude the 4 and 3 On Thu, 14 Aug 2003 19:38:07 -0700, "nobody" <Scruff57@notreallyhotmail.com> wrote: >Is there a way to sum a group of numbers based on whether it falls within a >specific. >For example. Suppose I want to find the average of ...

Microsoft Access Table Record Limitation
Hi, is there a limit to how may rows an access table can have before it performs poorly? "mark" <user@msgroups.net/> wrote in message news:OGxT6vdgKHA.2596@TK2MSFTNGP04.phx.gbl... > Hi, is there a limit to how may rows an access table can have before it > performs poorly? > > --- > frmsrcurl: http://msgroups.net/microsoft.public.access/ That's depends upon the type and quality of indexes, as well as other factors. I have a database front-end that connects to a 1.1 GB backend with 6 million records. By using the primary key, I can pull 1 record ...

Cannot change criteria on copied chart without changing original c
I have a chart that I created from a pivot table. Instead of creating 40 plus charts individually, I tried to copy the original chart and change the location for each chart. When I change the facility location for one chart it changes all others. How can I get around this? Steve You'll need to make independent pivot tables for each chart. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services 774-275-0064 208-485-0691 fax jon@peltiertech.com http://PeltierTech.com/ _______ "Steve" <Steve@discussions.microsoft.com> wrote in message news:07FF39D...