Solved

Very Strange behavior in Query in access

Posted on 2013-05-11
15
441 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
[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
  • 5
  • 2
  • +2
15 Comments
 
LVL 50

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 50

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 40

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 39258510
You are welcome!

/gustav
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

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