Count across fields, not down records

[Gastrifitis]

All right, I give up. I thought this would be simple, but it's not happening.

I'm trying to translate a Works file to Access. One of the fields caculates how much a job is worth based on how frequently the job is done. In Works, the calcuation look like this:

=COUNT('Week1','Week2','Week3','Week4','Week5','Week6','Week7','Week8')/8*'price'

If an entry is in the field Week1 through Week8 for that particular record, it gets counted by the function. If the field for that particular record is blank, it doesn't get counted. In Excel, the formula would be exactly the same except it would refer to cells instead of field names. But I can't get this to work in Access at all. Every version of Count in Access looks down the entire field to do its count, not just at the current record. I even tried using an IIF statement for every field, returning a 1 or 0 based on whether or not it contains "" and then totaling. Nothing works. How can you count across instead of down?

 

[CT Witter]

I think this may be of some use:

MS KB Article: 209839 - ACC2000: How to Calculate Row-Level Statistics
Although Microsoft Access has several built-in functions that enable you to perform statistical analysis across records, it does not have a built-in function to perform statistical analysis across multiple columns within a single row. This article shows you how to create several sample user-defined functions that you can use to get row-level statistics in Access 2000.

http://support.microsoft.com/default.aspx?scid=kb;en-us;209839&Product=acc2000

HTH,
CT