Solved

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

Posted on 2014-03-12
5
322 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:Srinivas Mantha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Srinivas Mantha
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
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 …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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