Solved

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

Posted on 2014-03-12
5
320 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
[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: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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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 …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

752 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