Solved

Very Strange behavior in Query in access

Posted on 2013-05-11
15
420 Views
Last Modified: 2013-06-18
I have attached sample file with the weird behavior,

i have 2 same table data (tblNormal,tblStrange)
also same query for both,
1 query have more result than the other!
how comes?, and how to solve this problem, its got me crazy!! :}
in the StrangeQuery query u will find that the field "always_scan" that have the value "1" which doen`t meet the query request is in the result!!
but in the same "Normalquery" it doesn`t

thnx alot.
Sample.accdb
0
Comment
Question by:drtopserv
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39158337
In one you use "or", in the other "and", so this is not so strange:

WHERE (((tblStrange.always_scan)=0) AND ((tblStrange.isIsky)="true")) OR (((tblStrange.is910)="true")) OR (((tblStrange.sug_mismach_mezahe_lak) In (5,6)));

WHERE (((tblNormal.always_scan)=0) AND ((tblNormal.isIsky)="true")) OR (((tblNormal.is910)="true") AND ((tblNormal.sug_mismach_mezahe_lak) In (5,6)));

/gustav
0
 

Author Comment

by:drtopserv
ID: 39158502
Oh sorry it`s my mistake , forget wat i say, then look at the one with the oR , mean with the result of 2077 rows.

this is a wrong answer, the right answer for me should be 2070 instead of 2077, means the remain 7 which are with the always_scan=1 should not be included,,,the Q is y the query include always_scan=1 (7 rows in total) in the result?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39158641
You need this adjustment:

SELECT
  tblStrange.r_object_id,
  tblStrange.always_scan,
  tblStrange.isIsky,
  tblStrange.is910,
  tblStrange.sug_mismach_mezahe_lak,
  tblStrange.action
FROM
  tblStrange
WHERE
  (tblStrange.always_scan=0 AND tblStrange.isIsky="true")
  OR
  (tblStrange.is910="true")
  OR
  (tblStrange.always_scan=0 AND tblStrange.sug_mismach_mezahe_lak In (5,6));

/gustav
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:drtopserv
ID: 39158895
OHH, y u devide the query in both? twise OR?
y couldn`t i do it as i wrote it before?
0
 
LVL 39

Expert Comment

by:als315
ID: 39159106
Can you show problem exactly? tblStrange has one extra record. If I rename tables (strange to normal and vice versa) results are same.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39159189
The way you did before didn't provide the desired result - so no, you couldn't write it like that.

/gustav

PS: This is not a chat room, and English isn't my native language, so could you please write English the normal way?
0
 

Author Comment

by:drtopserv
ID: 39159306
well, sorry for my english,

i want to go for  cactus_data select query, my question is why should i put another:
 OR
 (tblStrange.always_scan=0 AND...

means, you write the "OR (tblStrange.always_scan)=0" twise.
Why can`t i write it :
SELECT
  tblStrange.r_object_id,
  tblStrange.always_scan,
  tblStrange.isIsky,
  tblStrange.is910,
  tblStrange.sug_mismach_mezahe_lak,
  tblStrange.action
FROM
  tblStrange
WHERE
  (tblStrange.always_scan=0 AND tblStrange.isIsky="true")
  OR
  (tblStrange.is910="true")
  OR
  tblStrange.sug_mismach_mezahe_lak In (5,6));
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 39166553
Because 3 conditions separated by OR are treated independently, i.e. the query will get the rows that satisfy 1st condition   (tblStrange.always_scan=0 AND tblStrange.isIsky="true"), plus those that satisfy 2nd condition   (tblStrange.is910="true"), plus those that satisfy 3rd condition (  tblStrange.sug_mismach_mezahe_lak In (5,6)) )

If you include tblStrange.always_scan=0 in the 1st condition only, then all records with tblStrange.is910="true" will be included, regardless of tblStrange.always_scan. Same with 3rd condition.


Compare to this:

find all people who (are blondes and have green eyes) or (who have blue eyes).

In the result the people will blue eyes will have all colors of hair, not only blondes.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39166763
sometimes it is simply easier on the brain to repeat a small piece of code :) and the where clause suggested by cactus_data is very easy to understand - a good thing.
a good query optimizer would in many cases not care too much if you repeat the condition really.

However I think you might be able to avoid the repetition but I'm not certain of the access syntax, like this:
SELECT
      tblStrange.r_object_id
    , tblStrange.always_scan
    , tblStrange.isIsky
    , tblStrange.is910
    , tblStrange.sug_mismach_mezahe_lak
    , tblStrange.action
FROM tblStrange
WHERE (
        tblStrange.always_scan = 0
        AND (
            tblStrange.isIsky = "true"
            OR tblStrange.sug_mismach_mezahe_lak IN (5, 6)
            )
        )
    OR tblStrange.is910 = "true"
    
/* access loves unneeded brackets I think - but it may be wrong, don't use access */    
WHERE (
        (tblStrange.always_scan = 0)
        AND (
            (tblStrange.isIsky = "true")
            OR (tblStrange.sug_mismach_mezahe_lak IN (5, 6))
            )
        )
    OR (tblStrange.is910 = "true")
    

Open in new window

0
 

Author Comment

by:drtopserv
ID: 39168546
vadimrapp1,
I still can`t figure it out, from what you said the following line will accept only 1-2 AND Only :

find all people who (are blondes and have green eyes) or (who have blue eyes).

notice that the following code give right answer :

find all people who (are blondes and have green eyes OR have blue eye) /* the OR here related to people with blond and green eyes!

WHERE (
       (tblStrange.always_scan = 0) /* Are Blond
        AND (
            (tblStrange.isIsky = "true") /*have green eyes
            OR (tblStrange.sug_mismach_mezahe_lak IN (5, 6)) /*have blue eyes
            )
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 39168585
This is valid:

WHERE
  (tblStrange.always_scan=0 AND tblStrange.isIsky="true")
  OR
  (tblStrange.is910="true")
  OR
  (tblStrange.always_scan=0 AND tblStrange.sug_mismach_mezahe_lak In (5,6));

It can be altered to:

WHERE
  (tblStrange.always_scan=0
    AND
  (tblStrange.isIsky="true" OR tblStrange.sug_mismach_mezahe_lak In (5,6)))
  OR
  (tblStrange.is910="true")

One reason for the chosen syntax is to be able to display it in the GUI query designer.

/gustav
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
ID: 39168661
"find all people who (are blondes and have green eyes OR have blue eye) "

blonde, green eyes -> included
blonde, black eyes -> not included
brunette, blue eyes -> included

I.e. the #3 is included even though he is not blonde. So, if you want to include only blondes having one of two eye colors, you'll have to specify "are blondes" with each eye color separated by OR:

"find all people who (are blondes and have green eyes OR are blondes and have blue eyes) "

which is equivalent to

"find all people who (are blondes and (have green eyes OR have blue eyes) )"


This was the answer to your question "why should i put another:
 OR
 (tblStrange.always_scan=0"
0
 

Author Closing Comment

by:drtopserv
ID: 39258505
Well, from the answer of  vadimrapp1 and cactus_data, be able to understand the logic in this.
thnx alot guyS:}}}}}
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39258510
You are welcome!

/gustav
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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