CODEWITHSUNDEEP
sqlms-access-2010
David Monaghan
David Monaghan

Reputation: 43

Exclude records from query where some columns are all not equal to a value

I am relatively new to ACCESS 2010 and to SQL. Having a bit of trouble figuring this one out.

I have a a query that returns a result from a table. I want to exclude any records where the values in columns A1-A4 all equal zero.

ex:

+----+----------+----+----+----+----+
| ID | CustName | A1 | A2 | A3 | A4 |
+----+----------+----+----+----+----+
|  1 | Ben      |  0 |  0 |  0 |  0 |
|  2 | Tom      |  0 |  0 |  0 |  1 |
|  3 | Jerry    |  0 |  0 |  2 |  0 |
|  4 | Steve    |  0 |  3 |  0 |  0 |
|  5 | Dave     |  4 |  0 |  0 |  0 |
|  6 | Mike     |  0 |  0 |  2 |  1 |
|  7 | Sheila   |  0 |  3 |  2 |  1 |
|  8 | Jonesy   |  4 |  3 |  2 |  1 |
+----+----------+----+----+----+----+

Using the table above as the source for the query, the only record I want to be excluded should be the very first record.

The SQL I am using for this query is 

SELECT Test.CustName, Test.A1, Test.A2, Test.A3, Test.A4
FROM Test
WHERE (((Test.A1)<>0) AND ((Test.A2)<>0) AND ((Test.A3)<>0) AND ((Test.A4)<>0));

This SQL will only return records where all columns from A1-A4 are NOT equal to zero and only returns the final record. It should return record 2 - 7.

Upvotes: 2

Views: 2285

Answers (3)

dnoeth
dnoeth

Reputation: 60472

If those values are not negative you can also do

where A1+A2+A3+A3 > 0

Upvotes: 0

Matt
Matt

Reputation: 15061

Use OR

SELECT t.CustName, t.A1, t.A2, t.A3, t.A4
FROM Test t
WHERE (((t.A1)!=0) 
OR ((t.A2)!=0)
OR ((t.A3)!=0) 
OR ((t.A4)!=0));

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You want OR, not AND:

SELECT Test.CustName, Test.A1, Test.A2, Test.A3, Test.A4
FROM Test
WHERE (((Test.A1)<>0) OR ((Test.A2)<>0) OR ((Test.A3)<>0) OR ((Test.A4)<>0));

Upvotes: 1

Related Questions