Solved

MS Access Select Query SQL with OR in two different fields & AND

Posted on 2014-03-12
5
316 Views
Last Modified: 2014-03-13
Can any one correc the following MS Access SQL. The output should result in dataset
that would meet the criteria of rrcode field = 1 OR osreportcode =1 & AND with other field criteria.  The following code is not giving the desired output

string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1  AND wtcode=0 AND (rrcode=1 OR osreportcode=1)  order by pid ";
0
Comment
Question by:smanthanims
5 Comments
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 125 total points
ID: 39925604
"SELECT * from tbldemolab WHERE ( freezecode=1  AND wtcode=0 AND rrcode=1) OR (freezecode=1  AND wtcode=0 AND osreportcode=1)  order by pid ";
0
 
LVL 17

Accepted Solution

by:
Kent Dyer earned 125 total points
ID: 39925625
-- PERFORMANCE TIP: RUN YOUR "UNUSUAL" TESTS FIRST TO FLUSH OUT INCORRECT RESULTS.
I think this what you are asking for..

Think of it this way:  Tommy and Betty and Suzy - so the condition has to be where all three are in the result set (limiting results).
Tommy or Betty or Suzy - so the condition has to be where all there own independent result set (expanding results).

You are asking..

Tommy and Betty and Suzy
or
David and Betty and Suzy

the results are going to be with two different sets: Tommy, Betty, Suzy in the first set as well as: David, Betty, Suzy

Let's re-write your query:
SELECT *
FROM   tbldemolab
WHERE  ( ( rrcode = 1
           AND freezecode = 1
           AND wtcode = 0 )
          OR ( osreportcode = 1
               AND freezecode = 1
               AND wtcode = 0 ) )
ORDER  BY pid  

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39925638
Your SQL statement looks fine except for the semicolon you have after the ending quote.  You should put the semicolon inside the quoted string or dispense with it altogether.
string sqldemolablh = "SELECT * from tbldemolab WHERE freezecode=1 AND wtcode=0 AND (rrcode=1 OR osreportcode=1) order by pid;"

Open in new window

Ron
0
 

Author Closing Comment

by:smanthanims
ID: 39925781
Prompt and timely solution. That semicolon is actually an error here. I was coding through ASP.NET c# defining the string for SQL. Now the code is
sqldemolablh = " SELECT * FROM   tbldemolab WHERE  ( ( rrcode = 1 AND freezecode = 1  AND wtcode = 0 )  OR ( osreportcode = 1 AND freezecode = 1  AND wtcode = 0 ) ) ORDER  BY pid ";
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39926398
I don't understand why the change was needed. Both WHERE comparisons are logically equivalent.  Look at these 2 sentences:

Can Betty and Suzy and (Tommy or David) join our team?
Can (Betty and Suzy and Tommy) or (Betty and Suzy and David) join our team?

Ron
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now