• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Help with elimination query (1 Viewer)

jocph

Member
Local time
Today, 08:52
Joined
Sep 12, 2014
Messages
52
Hi all!

I know this is simple but I cannot make it to give me the result I need.
Say I have a table like this:
1598509770371.png


How do I make the query not to include record 4 with all 0's in fields Number1, Number2, Number3 and Number4?

I tried this:
Code:
SELECT tblTemp.ID
FROM tblTemp
WHERE (((tblTemp.Number1)<>0) AND ((tblTemp.Number2)<>0) AND ((tblTemp.Number3)<>0) AND ((tblTemp.Number4)<>0));
but it gives me no record.

Appreciate the help!
 

arnelgp

error reading drive A:
Local time
Today, 08:52
Joined
May 7, 2009
Messages
10,620
where (number1 + number2 + number3 + number4) <> 0
 

jocph

Member
Local time
Today, 08:52
Joined
Sep 12, 2014
Messages
52
where (number1 + number2 + number3 + number4) <> 0

Of course! I know it should be simple. Thanks arnelgp.

I would like to understand though why my sample query does not give any record?
 

arnelgp

error reading drive A:
Local time
Today, 08:52
Joined
May 7, 2009
Messages
10,620
you are using and with the condition that not equal to zero.
to make the explanation short you are saying that all Columns must not be equal to 0, which will not produce result
since there is at least a column with 0 on it.
 

jocph

Member
Local time
Today, 08:52
Joined
Sep 12, 2014
Messages
52
I have to digest this to contrast with making the condition =0, which gives the correct record (which is 4). Thanks as always!
 

jocph

Member
Local time
Today, 08:52
Joined
Sep 12, 2014
Messages
52
good day
>table query creates auto sql

SELECT TableTemp.Number1, TableTemp.Number2, TableTemp.Number3, TableTemp.Number4
FROM TableTemp
WHERE (((TableTemp.Number1)>0) AND ((TableTemp.Number2)>0) AND ((TableTemp.Number3)>0) AND ((TableTemp.Number4)>0));

I'm sorry but your example stilll gives no record. As arnelgp explains, there would always be a record with a 0 in the fields, thus eliminating all.
 

vhung

Member
Local time
Yesterday, 17:52
Joined
Jul 8, 2020
Messages
144
I'm sorry but your example stilll gives no record. As arnelgp explains, there would always be a record with a 0 in the fields, thus eliminating all.

SELECT TableTemp.Number1, TableTemp.Number2, TableTemp.Number3, TableTemp.Number4, TableTemp.ID
FROM TableTemp
WHERE ((([number1]+[number2]+[number3]+[number4])<>0));

>see the attachment on query ID#4 iliminated which is (n1+n2+n3+n4)=0

>sir arnel is right...
"where (number1 + number2 + number3 + number4) <> 0"
 

Attachments

  • accdbQuery3.png
    accdbQuery3.png
    107.1 KB · Views: 10
  • accdbQuery2.png
    accdbQuery2.png
    60.7 KB · Views: 10
Last edited:

Users who are viewing this thread

Top Bottom