Count fields with non zero values

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

Guest

I have 4 number fields in a table: Field1, Field2, Field3, and Field4. The
default value of these number fields is zero. A user may or may not enter
data into these fields via a form. Is there a way in a table, query, or text
box control source, to count the fields (Field1, Field2, Field3, and Field4)
that are greater than 0? I have done this with code using the "on current"
event procedure on the form. I'm thinking I either have to wite that value
back another field in the table, or use some code in the "criteria" section
of a query, or code in the "default value" section of the table field. TIA
 
Is there a way in a table, query, or text
box control source, to count the fields (Field1, Field2, Field3, and
Field4) that are greater than 0?
Click to expand...

This really sounds like a Design Problem. Whatever these fields are
actually called or whatever they mean, if they are so interchangeable that
you can count them or compare them in any order, then they almost certainly
ought to be four records in a new table.

With your current design, you can do something like

SELECT IIf(Field1>0,1,0) + IIf(field2>0,1,0) +
IIf(field3>0,1,0) + Iif(field4>0,1,0) AS CountOfNonZeroFields
FROM MyTable
etc etc

but that just goes to show how poor the current design is.

Hope that helps


Tim F
 
These fields are their own fields in the table. I generically called them
field 1-4 beacause it was easier to type. In any case, a supervisor will
type how many line operators they had on an assembly line during the 4 hour
period (Field 1, Field 2, etc). Mangement wants to know what the average
head count is for the 4 hour period, but only if there are operators present
during those particular hours. The hours will be whole numbers (1, 2, 3,
etc. can't have a half a person). That is why I want the count of fields
that have numbers greater than zero, so I can use them in my average head
count calculation.
 
In any case, a supervisor will
type how many line operators they had on an assembly line during the 4
hour period (Field 1, Field 2, etc).
Click to expand...

As I thought: this really really really really sounds like it needs
normalising.

All the best


Tim F
 
As Tim says, you're headed for trouble with that design.

For example, what if management suddenly wants reporting in /6/ hour
periods? From the sound of it, you'd have to add new fields & rewrite
all your code. But in a properly designed database, you wouldn't have
to do either of those two things. What if management wanted reporting
in 4 hour periods /and also/ in 6 hour ones?

If you describe your data in plain language (avoiding technical terms
like table or field), we can thn suggest a table structure that would
be more aligned with proper database practices.

Believe me, designing the table structure is a very important part of
creating a database. It is way more tricky than I think you perhaps
understand :-)

HTH,
TC [MVP Access]
 
Back
Top