Solved

Very Strange behavior in Query in access

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

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 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

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

839 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