Community
Showing results for 
Search instead for 
Do you mean 
Reply

Help! writing an advanced query

Copper Contributor
Posts: 10
Country: USA

Help! writing an advanced query

I’m trying to write a query that basically combines to different queries.  Each works well separately but when I try to combine them it doesn’t work.  I’m trying to look up all Opportunities that were opened in January that are at stages 7 through 9 “plus” all the Opportunities that were Closed-Won in January regardless of when they were opened.

 

I know I need to put parenthesis in the right place.  I put a space between the two seperate queries and the "Or" is where I'm trying to connect them.  I put the last criteria in because

 

Any Ideas are greatly appreciated

 

(Opportunity       Status                                 Equal to (=)         1                                              And

Opportunity       Actual Close Date             On or After           1/1/2011                                And

Opportunity       Actual Close Date             On or Before       1/31/2011            )                Or

                                                               

Opportunity       Open Date                          On or After           1/1/2011                                And

Opportunity       Open Date                          On or Before       1/31/2011                              And

(Opportunity      Stage                                   Starts With          7                                              Or

Opportunity       Stage                                   Starts With          8                                              Or

Opportunity       Stage                                   Starts With          9                              )              End

Bronze Contributor
Posts: 1,393
Country: USA

Re: Help! writing an advanced query

Hello jbailey ....

 

I think if you make the following changes it will work:

1.  change the "or"  to "and" at the end of the 3rd line, that will combine the two sets.

2.  Add double (( before line 4

3.  Add single ) after line 5

4.  Add single ( before line 6

5.  Add double )) after line 8

 

I didn't test it, but I think that will group what you want.

John Purdy
ACT! Premium 2016 (V. 18)
Main: HP 9470M 8GB, Win 10 Pro, & Exchange 2013 & Office 365, 32bit
Remote: Dell XPS Ultrabook with 4GB & Win 10 Pro, Office 365 32bit & Exchange 2013
Copper Contributor
Posts: 10
Country: USA

Re: Help! writing an advanced query

Thanks jnpurdy,

 

Your solution was not quite correct but it lead me to the correct solution.  Your placement of parenthesis did the trick.  If I change the 'Or' on line 3 to 'And', records in the lookup have to meet the criteria in both groups to be included (so when I tried it there were only a few records in the lookup).  This was my first impression also.

 

When I put parenthesis where you suggested it worked great!

 

I know parenthesis tell the query what to do first.  Does that mean "((" are done before "("?

 

In your example, did the query search for all records where stage start with 7, 8 or 9 and then filter by date range or the other way around?

Bronze Contributor
Posts: 1,393
Country: USA

Re: Help! writing an advanced query

Regarding parenthesis, it is my understanding that the inner most parenthesis are completed before the outer and follows boolean logic.

 

Hope that helps.

John Purdy
ACT! Premium 2016 (V. 18)
Main: HP 9470M 8GB, Win 10 Pro, & Exchange 2013 & Office 365, 32bit
Remote: Dell XPS Ultrabook with 4GB & Win 10 Pro, Office 365 32bit & Exchange 2013
Bronze Super Contributor
Posts: 1,170
Country: USA

Re: Help! writing an advanced query

Yes, the deeper the nesting with (, the higher the precedence, or rather the earlier it is evaluated...