Solved Help with elimination query (1 Viewer)

jocph

Member
Local time
Today, 15:09
Joined
Sep 12, 2014
Messages
61
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:09
Joined
May 7, 2009
Messages
19,169
where (number1 + number2 + number3 + number4) <> 0
 

jocph

Member
Local time
Today, 15:09
Joined
Sep 12, 2014
Messages
61
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

..forever waiting... waiting for jellybean!
Local time
Today, 15:09
Joined
May 7, 2009
Messages
19,169
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, 15:09
Joined
Sep 12, 2014
Messages
61
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, 15:09
Joined
Sep 12, 2014
Messages
61
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
Today, 00:09
Joined
Jul 8, 2020
Messages
235
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: 102
  • accdbQuery2.png
    accdbQuery2.png
    60.7 KB · Views: 85
Last edited:

Users who are viewing this thread

Top Bottom