• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

FIND logical order for multiple AND/OR criteria

I need to query my db for several items. I regularly use Constrain, Extend, and Omit in isolated cases, but I need help with the order and syntax to combine AND/OR for complex searches.
Query:
Procedure A or B [ProcA OR ProcB] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)
AND [1 OR 3] from {1,2,3,4,5} (from a radio button field where I really want NOT [2 OR 4 OR 5]).

Thanks,

Mark
0
MarkJulie
Asked:
MarkJulie
  • 3
  • 3
1 Solution
 
Will LovingPresidentCommented:
Hi Mark - It sounds like this will involve several similar Find requests or Find followed by a Constrain. The general rule with the order of Find Requests, is to always Find what you want first and then use Omit or Constrain afterwards. (I use Extend sometimes, but very rarely compared to Omit or Constrain). There are probably many ways to get what you want but here's one possible order based on what you've described:

First Find request (FIND):

Procedure A [ProcA ] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)

Second Find request (FIND):

Procedure B [ProcB ] (from two different fields that either have a date or are NULL)
AND Location [MyTown] (from a checkbox list of locations)
AND Time 1/1/2010...12/31/2010 (from a date field)

Third Find request (OMIT):

[2] from {1,2,3,4,5} (from a radio button field

Fourth Find request (OMIT):

[4] from {1,2,3,4,5} (from a radio button field

Fifith Find request (OMIT):

[5] from {1,2,3,4,5} (from a radio button field

Requests 3-5 could also be done as a Constrain after the initial find, either Omitting 2, 4, and 5 or Finding the two you want, 1 and 3.
0
 
MarkJulieAuthor Commented:
Willmcn,

I tried the steps you suggested, but I am misusing the results of the two Find requests. I get 67 records when I set criteria for the first Find, Duplicate Request and change from ProcA=* to ProcB=* leaving the date range 1/1/2010...12/31/2010 and Location = MyTown in both requests.

After setting criteria for each Request, I run Find and get 67 records.

At this point, I run Modify Last Find (emptying Date, Procedure, and Location criteria) changing to Omit=2 from list {1,2,3,4,5} and Constrain Found Set.

Instead of getting <=67 records, I get 5009.

What do I need to do differently?

For further clarification, does adding additional requests accumulate into one large OR Find?

What is the difference between Add New Request and Extend Found Set for my situation?

This may take several rounds. Apologies for being offline several days of the week and slow response on my end.
0
 
Will LovingPresidentCommented:
Don't use Modify Last Find, but do Constrain Found Set [] including the two Omit Find Requests. this keeps your find within the found set. (I have to go out for a bit but will respond more fully when I return).
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
MarkJulieAuthor Commented:
I got a partial solution, but I await your further comments.
0
 
Will LovingPresidentCommented:
> What do I need to do differently?

As noted previously, do not use "Modify Last Find" but instead use a single "Constrain Found Set [ Restore ]" step that contains the three Omit Records Requests as noted above.

> For further clarification, does adding additional requests accumulate into one large OR Find?

In general, doing multiple requests is effectively one large OR Find. It is also critical that if you do large Find with multiple requests, that any "Omit Records" requests come AFTER all the "Find Records" requests. I'm not sure why the particular steps you got led to more records if the last script step was a Constrain Found Set but leaving out the Modify Last Find and simply storing the Omit Records Requests with the Constrain Found Set step should give you what you are looking for.


> What is the difference between Add New Request and Extend Found Set for my situation?

In this situation, I'm not sure there is any difference between the two. The Extend Found set simply lets you add the results of a new Find (which can include many Find Requests) to the existing Found Set. If in your initial Find you can include those Find Requests (presumably before any omits) then it should have a similar effect.

The difference might be if you had a group of requests that included both Find and Omit Records that you performed initially, but then you wanted to Extend that found set with a search criteria and did NOT want to have any Omits applied to it. In that instance, if the Omit Records requests in the first Find would have affected those additional records, using a second script step to Extend the Found set would be correct.

One thing to note: I've been assuming that you are scripting this Find since it is multiple steps. That's why I've made reference to the "Constrain Found Set [ Restore ]" script step. If you are simply performing this manually, then you should do the following:

Perform the initial two Request Find as you described by creating the first Find Request and then duplicating it
Go back into Find Mode instead of using Modify Last Find
Create the three Omit Records Find Requests
Perform a Constrain Found Set
0
 
MarkJulieAuthor Commented:
Thanks for the additional teaching to save noob questions in the future for me and others.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now