Counting only records that have values > 1

[Shawn Wenger]

I am trying to count records from two fields that only have a value greater
than 0. Only one of these fields is used per day so when it is not used, the
value is defaulted to 0. I need to show what days were in use in regards to
entering a begin date and end date. Can someone help me please. I don't work
with access to often. I can not change the query that get this data. When I
use the Count(*), I get a count of all of the records. I need to distinguish
how many were actually used( >0).

 

[John Spencer]

You can use one of the following expressions

Count(IIF(SomeField>0,1,Null))

or

Abs(Sum(SomeField>0))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

Shawn

Are you saying that your table has multiple fields, only one of which is
"used per day"?

If so, that sounds more like a spreadsheet than a relational database table.

If you can provide an example of this/these field(s), folks here may be able
to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Shawn Wenger]

Jeff

This is an example of my fields with in the Report "[Well_Total_Well_1]"
and "[Well_Total_Well_2]" . Depending on the operation, only one of these
two fields of the table contain usable data. In the Detail of the report,
both of these two fields are shown for each day. One of the fields will
contain a '0' and the other some total value. I need to show the Days in use
by depicting whether there was a usable value (not 0) for a determined amount
of days (monthly, etc.). I tried the suggested expressions to no avail. I
have no way to edit the Query to express viewable data, it will always be 0
or some value. I need to eliminate from the Count, the days with 0 in them. I
hope this helps explain. Any ideas will be greatly appreciated.

Thanks,

 

[Jeff Boyce]

Shawn

To save yourself (and those who need to understand your db later) some
headaches, consider stepping back for a bit and spending a bit more time
normalizing your data. If you have "repeating fields" ([Well_Total_Well_1],
[Well_Total_Well_2]), you have a design more like a spreadsheet than a
relational database table.

What you are asking to do is fairly simple ... if you are working in a
spreadsheet.

But if you want to identify which "thing" was done on a particular day using
a relational database like MS Access, you'll need to learn a new way to
organize the data. This is because the way that Access' features and
functions work, they expect relational data.

Besides, if you "add" one more field/thing that was done in your design,
you'd need to add another field, modify your forms, queries, reports, code,
macros, etc. ... what a headache!

A well-normalized table adds rows, not columns.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff

This is an example of my fields with in the Report "[Well_Total_Well_1]"
and "[Well_Total_Well_2]" . Depending on the operation, only one of these
two fields of the table contain usable data. In the Detail of the report,
both of these two fields are shown for each day. One of the fields will
contain a '0' and the other some total value. I need to show the Days in
use
by depicting whether there was a usable value (not 0) for a determined
amount
of days (monthly, etc.). I tried the suggested expressions to no avail. I
have no way to edit the Query to express viewable data, it will always be
0
or some value. I need to eliminate from the Count, the days with 0 in
them. I
hope this helps explain. Any ideas will be greatly appreciated.

Thanks,

Shawn

Are you saying that your table has multiple fields, only one of which is
"used per day"?

If so, that sounds more like a spreadsheet than a relational database
table.

If you can provide an example of this/these field(s), folks here may be
able
to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP