Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

Very Strange behavior in Query in access

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
drtopserv
Asked:
drtopserv
  • 5
  • 5
  • 2
  • +2
2 Solutions
 
Gustav BrockCIOCommented:
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
 
drtopservAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
drtopservAuthor Commented:
OHH, y u devide the query in both? twise OR?
y couldn`t i do it as i wrote it before?
0
 
als315Commented:
Can you show problem exactly? tblStrange has one extra record. If I rename tables (strange to normal and vice versa) results are same.
0
 
Gustav BrockCIOCommented:
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
 
drtopservAuthor Commented:
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
 
Vadim RappCommented:
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
 
PortletPaulCommented:
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
 
drtopservAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
 
Vadim RappCommented:
"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
 
drtopservAuthor Commented:
Well, from the answer of  vadimrapp1 and cactus_data, be able to understand the logic in this.
thnx alot guyS:}}}}}
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now