Query for not 0

[Martin]

I am having a problem with a query i thought would be easy. My table looks
like this:

Urban Suburban Rural
1 0 0
0 1 0
0 0 0

The key is that some records have zeros in all three fields. I want to do a
query that will return only those records that DO NOT have zeros in all
three fields.

When I tried <>0 in the criteria row of a query for all three fields i get
nothing.

What am i doing wrong?

 

[Jeff Boyce]

Martin

Another way to look at this is that the sum of the three fields will be >0
if ANY of the fields is not zero.

Have you tried testing the sum?

(by the way, the use of 1 & 0 and the field names could be construed to
represent true/false fields for multiple categories. If this is your
situation, the table you describe is a ... spreadsheet! You'd pretty much
have to do it that way if you were using Excel, but if I read between the
lines, you could theoretically have zero or one or ... three different
categories checked (if you are using Yes/No fields).

I suspect you have a many-to-many relationship (what your rows are about, as
related to the categories) In a relational database, you use one table to
store whatever your rows are about, one table to store the categories, and a
third table to show which category(ies) belong with which (whatever your
rows are about).

Or maybe I just read too much into your example...<g>

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Martin]

How exactly would i test for the sum? I don't understand.

Yes, the table is weird. It comes straight from another program. The 1s and
0s are indeed yes and no answers.

 

[pietlinden]

SELECT Urban, Suburban, Rural
FROM MyTable
WHERE Urban+Surburban+Rural>0;

 

[Martin]

THANKS TO BOTH OF YOU.

THAT WORKS!