Count number of fields that contain zero's

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
I have a table with number field's 0 through 6. I then need to count all the
fields that have a "0" in them and have that total on another field. Is this
possible?

Thanks for your help
 
I'm not sure I'm visualizing your table structure...

You have a table with 7 fields numbered "0" through "6"? Do they all
contain the same kind of data? What does it mean to have a value of "0" in
the "2" field, but not a value of "0" in the "3" field? What other values
do you have in these fields?

I'm asking because it may be that there's a more-normalized design that
would make your counting easier. For example, if you have a "0" in "0", and
another in "1", and another in "2", would you want your count to be 3? And
if, in the next row, you have another "0" in "2", should the count be 4?

Have you looked into using a query, particularly a "Totals" query?

More info, please...

Jeff Boyce
Microsoft Office/Access MVP
 
Try something like in a new field

CountOf_0:
Abs(Field1=0)+Abs(Field2=0)+Abs(Field3=0)+Abs(Field4=0)+Abs(Field5=0)+Abs(Field6=0)
 
it is structured like this

a 16
b 39
c 0
d 0
e "count of number of a through d that have zero's in them"

thanks
 
Where do "a", "b", ... fit into your earlier description?

Is there a chance you could use Excel to do the counting?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If the number of fields that could have "0" for a value can change, I'll
strongly recommend that you look into further normalization for your table
structure.

If you could add your data as new rows, rather than as new columns (fields),
counting would be as simple as ... well, ... 1-2-3!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
those are generic names of the fields

Jeff Boyce said:
Where do "a", "b", ... fit into your earlier description?

Is there a chance you could use Excel to do the counting?

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Try this

Field 1 - 18
Field 2 - 39
field 3 - 0
field 4 - 0
field 5 - "Count of number of fields with a "0" in them" - which would equal 2
 
I am not using excel because the amount of zero's in my fields will work
towards later calculations
 
That does not work for what I was trying to setup. Can you look through all
my posts again?
Thanks
 
That should have worked, unless you are talking about records and not fields.
Does the records in the table look like that

Field1 Field2 Field3 Field4
2 2 0 0

Or like
FieldName
2
2
0
0
 
Back
Top