Solved

Very Strange behavior in Query in access

Posted on 2013-05-11
15
404 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

705 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

17 Experts available now in Live!

Get 1:1 Help Now