Count non-nil values

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

Guest

My report has a field for the date at the end of each week, and fields for
each day of the week. When a day is worked a X is included in the day of the
week. I want to be able to count the days worked in each week, and then total
all the days worked over the period of weeks that I select through a
parameter query.

Regards
 
pkeegs said:
My report has a field for the date at the end of each week, and fields for
each day of the week. When a day is worked a X is included in the day of the
week. I want to be able to count the days worked in each week, and then total
all the days worked over the period of weeks that I select through a
parameter query.
Click to expand...


Your table actually has fields like Day1, Day2, ... then you
should call timwout a normalize your table structure.

If the fields really have Null and not a zero length string,
then you can count the **records** with a non-null in the
field by using a text box expression =Count(Day1) in a
group amd/or the report footersection. The total count for
the Report would then be:
=Count(Day1) + Count(Day2) + ...

If each of your records has fields for an entire week then
the toal for one record would be:
=Nx(Day1), 0) + Nx(Day2), 0) + ...
 
Thanks Marshall,

I have no problem counting the total non-null records. The Count expression
works well enough. I don't understand the syntax of yourother expression
using Nx but I inserted that instead of the count without any results - a
message box asked for a value for Nx. The count within the detail of a report
I need is as below where each line of X equates to a different record within
the detail. Each day of the week is a different field (There is a specific
purpose in this report which we are currently manually calculating for each
week).

End Date Mon Tue Wed Thur Frid Sat Sun
02/04/06 X X X X X (Count =5)
09/04/06 X X X X (Count =4)

I hope this gives a little more information to go on

Regards
 
Another case of fumble fingers. That was supposed to be NZ
not Nx. Check out the Nz function in Help, it is very
useful when dealing with Null values.
 
Hi Marshall,

Quite an interesting little function. However it only concatenates the
values across the row, my example, "XXXXX" & "XXXX", it doesn't count them. I
tried replacing the X with a number hoping it would add them, but again it
concatenates, similarly if I use '&' instead of '+'. If I introduce 'Count'
anywhere it returned the total record count. I would welcome any further
comment

Regards

Marshall Barton said:
Another case of fumble fingers. That was supposed to be NZ
not Nx. Check out the Nz function in Help, it is very
useful when dealing with Null values.
--
Marsh
MVP [MS Access]

I have no problem counting the total non-null records. The Count expression
works well enough. I don't understand the syntax of yourother expression
using Nx but I inserted that instead of the count without any results - a
message box asked for a value for Nx. The count within the detail of a report
I need is as below where each line of X equates to a different record within
the detail. Each day of the week is a different field (There is a specific
purpose in this report which we are currently manually calculating for each
week).

End Date Mon Tue Wed Thur Frid Sat Sun
02/04/06 X X X X X (Count =5)
09/04/06 X X X X (Count =4)

I hope this gives a little more information to go on
Click to expand...
Click to expand...
 
If + is concatenating the values, then the values are text
strings. I realize now that when you said there is an X in
the day field, that's what you meant. Somehow, I had
thought that X represented a number.

Let's start over. If each day can be either "X" or Null,
then a simple expression can count the Xs in a week:
=Len(Day1 & Day2 & Day3 & ...)

You xab get a grand total of Xs in the entire report by
adding another text box to the detail section. Set its
control source expression to =nameofweekcountbox and
RunningSum property to Over All. Then the report footer can
display the grand total by referring to the running sum text
box.

If I'm still misunderstanding, please post some more details
about the values you have in the day fields
 
Thanks Marshall, that was spot-on and the whole report is now functioning
nicely - plus I have learnt a few tips along the way.

Regards

Marshall Barton said:
If + is concatenating the values, then the values are text
strings. I realize now that when you said there is an X in
the day field, that's what you meant. Somehow, I had
thought that X represented a number.

Let's start over. If each day can be either "X" or Null,
then a simple expression can count the Xs in a week:
=Len(Day1 & Day2 & Day3 & ...)

You xab get a grand total of Xs in the entire report by
adding another text box to the detail section. Set its
control source expression to =nameofweekcountbox and
RunningSum property to Over All. Then the report footer can
display the grand total by referring to the running sum text
box.

If I'm still misunderstanding, please post some more details
about the values you have in the day fields
--
Marsh
MVP [MS Access]


Quite an interesting little function. However it only concatenates the
values across the row, my example, "XXXXX" & "XXXX", it doesn't count them. I
tried replacing the X with a number hoping it would add them, but again it
concatenates, similarly if I use '&' instead of '+'. If I introduce 'Count'
anywhere it returned the total record count. I would welcome any further
comment
Click to expand...
Click to expand...
 
Well, that is good news, especially after I got off on the
wrong foot.

I do want to emphasize that if your report is indicative of
a table in your database, then you are only starting to run
into trouble with that arrangement. You should not have
fields like Day1, Day2, ... in a table which violates a
fundamental rule of data normalization. Instead you should
have separate rows in another table for each day (and no
records for days not worked). Unfortunately, that will make
a data entry form such as yours more difficult to do. If
you leave the table the way it is, be prepared to run into
other problems that require unusual workarounds or even
unsolvable conflict with your objective. It would be good
for you to keep in mind that a database is nothing like a
spreadsheet.
 
Thanks for those comments Marshall,
unfortunately I don't have much more than a basic understanding of Access
and when you talk about 'fundamental rules of data normalisation' I cringe a
bit. The table I have been working on is a late attachment to the main data
base and I admit I can visualise some issues, not the least being the sheer
size it will grow into. I may have to spend time each year simply deleting
records for a prior year. The course that I have done on Access has not given
me much more than a basic understanding of how a database works and didn't
really touch on such things as 'data normalisation'. (The helpscreens are not
always particularly helpful either!) I would be open to investing in a good
resource if you could make some suggestions.

Regards

Marshall Barton said:
Well, that is good news, especially after I got off on the
wrong foot.

I do want to emphasize that if your report is indicative of
a table in your database, then you are only starting to run
into trouble with that arrangement. You should not have
fields like Day1, Day2, ... in a table which violates a
fundamental rule of data normalization. Instead you should
have separate rows in another table for each day (and no
records for days not worked). Unfortunately, that will make
a data entry form such as yours more difficult to do. If
you leave the table the way it is, be prepared to run into
other problems that require unusual workarounds or even
unsolvable conflict with your objective. It would be good
for you to keep in mind that a database is nothing like a
spreadsheet.
--
Marsh
MVP [MS Access]

Thanks Marshall, that was spot-on and the whole report is now functioning
nicely - plus I have learnt a few tips along the way.

Regards
Click to expand...
Click to expand...
 
Back
Top