Lost on how to average a record that may contain blank fields

[fateddarkness]

Hello! I'm creating a database for a tracking report that will hold the scores of all my company's employees. They are graded on certain criteria, however sometimes a criterion may not apply and would be left blank. If it does apply, the employee receives either a 100% or 0%, there are no partials.

There are 41 criteria in 7 categories in a datasheet called 'Scores'. 'Scores' is set up like the following:

ID | EMPLOYEEID |CLIENTID | PURCHASEORDER | 1A | 1B | 1C | 2A | 2B | 2C...(and so forth)



The fields entitled 1A, 2A, and such are the columns that will contain the score for each criteria. I used this lengthy formula before I realized the blank fields would cause an error:

=(([1A]+[1B]+[1C]+[1D]+[1E]+[1F]+[1G]+[1H]+[1I]+[2A]+[2B]+[2C]+[3A]+[3B]+[3C]+[3D]+[3E]+[3F]+[3G]+[4A]+[5A]+[5B]+[5C]+[5D]+[5E]+[5F]+[5G]+[5H]+[6A]+[6B]+[6C]+[7A]+[7B]+[7C]+[7D]+[7E]+[7F]+[7G]+[7H]+[7I]+[7J])/(41))

I also tried using Avg, but didn't quite understand how to make it work. I'm new to Access. Can anyone provide a solution for me? I was attempting to put this in a form so that as an analyst entered each individual score, the textbox would keep track of the overall percentage. However, I'll need a solution that will work for reports and queries as well. Thank you!

[June7]

Avg is an aggregate function. All intrinsic aggregate functions do calculations on a single field to 'aggregate' the data for a set of records.

Doing aggregate calcs for multiple fields of a single record requires an expression such as you are attempting. An expression can be a call to a custom function. Review http://support.microsoft.com/kb/209839

Your data structure is not normalized and will cause you a lot of frustrations. The scores should be in a related table. Each score should be a single record. Then you can use intrinsic aggregate functions on the records.

If the field is empty do you want it treated as 0 and included in the averaging or should it be excluded?

[fateddarkness]

Thanks for the link. I'm learning VBA, but I'm not very comfortable with it yet.

The scores are in a separate table that have a relationship with 'Employees' & 'Clients', so CLIENTID & EMPLOYEEID are lookup fields. PURCHASEORDER also has to be included as a reference. What do you mean when you say each score should be a single record? I don't know how to respond there, but it seems like you're saying each separate criterion would need its own record attached to the name of the employee, rather than all in one row.

The empty field should be excluded because it does not apply.

[Missinglinq]

You really need to heed June7's advice, but as to how to prevent the problem caused by 'blank fields,' you would use the Nz() function to tell Access what Value to use if a given Field is blank. The following code assigns a 0(Zero) to a Field that is blank:

=((Nz([1A],0) + Nz([1B],0) + Nz([1C],0) ...

Linq ;0)>

[Missinglinq]

...The empty field should be excluded because it does not apply...

Sorry, just saw the above. If you mean that when you divide to get your average score, you only divide by the number of Fields that have not been left blank, you need to do something like this:

In Form Design View, holding down the <Shift> key, click on each of the Controls to be counted and go to Properties – Other and enter Crit in the Tag Property (just like that, no Quotes).

Then use this code prior adding up the Values

Dim ctl As Control
Dim CritCount as Integer

CritCount = 0

For Each ctl In Me.Controls
      If ctl.Tag = "Crit" Then
            If Nz(ctl, "") <> "" Then
              CritCount = CritCount + 1
             End If
     End If
Next ctl

Once you get the total Values, you would divide this total by CritCount for the average of the filled in Fields.

Linq ;0)>

[fateddarkness]

Thank you for your help.

I'd like to try the solution you've given, but I'm not sure how I should enter my values into the code you've given me. Is it like this?:

Dim ctl As ControlDim CritCount as Integer


CritCount = 0


For Each ctl In Me.Controls
      If ctl.Tag = "Crit" Then
            If Nz(ctl, "") <> "" Then
              CritCount = CritCount + 1
             End If
     End If
Next ctl


=(([1A]+[1B]+[1C]+[1D]+[1E]+[1F]+[1G]+[1H]+[1I]+[2A]+[2B]+[2C]+[3A]+[3B]+[3C]+[3D]+[3E]+[3F]+[3G]+[4A]+[5A]+[5B]+[5C]+[5D]+[5E]+[5F]+[5G]+[5H]+[6A]+[6B]+[6C]+[7A]+[7B]+[7C]+[7D]+[7E]+[7F]+[7G]+[7H]+[7I]+[7J])/(Critcount))

As for June7's advice, I really would like to follow it...But I don't think I understand it completely.

[Missinglinq]

You'd need something in front of the equal sign, of course, and you have to use Nz() against each of the score Controls, as explained above, to keep the summing of the Controls from bombing:

Average = ((Nz([1A],0) + Nz([1B],0) + Nz([1C],0) ...)/(Critcount))

Linq ;0)>

[fateddarkness]

Okay, I've revised it:

Dim ctl As ControlDim CritCount as Integer
 
 
CritCount = 0
 
 
For Each ctl In Me.Controls
      If ctl.Tag = "Crit" Then
            If Nz(ctl, "") <> "" Then
              CritCount = CritCount + 1
             End If
     End If
Next ctl 
Average = ((Nz([1A],0)+ Nz([1B],0)+ Nz([1C],0)+ Nz([1D],0)+ Nz([1E],0)+ Nz([1F],0)+ Nz([1G],0)+ Nz([1H],0)+ Nz([1I],0)+ Nz([1J],0)+ Nz([2A],0)+ Nz([2B],0)+ Nz([2C],0)+ Nz([2D],0)+ Nz([3A],0)+ Nz([3B],0)+ Nz([3C],0)+ Nz([3D],0)+ Nz([3E],0)+ Nz([3F],0)+ Nz([3G],0)+ Nz([4A],0)+ Nz([5A],0)+ Nz([5B],0)+ Nz([5C],0)+ Nz([5D],0)+ Nz([5E],0)+ Nz([5F],0)+ Nz([5G],0)+ Nz([5H],0)+ Nz([6A],0)+ Nz([6B],0)+ Nz([6C],0)+ Nz([7A],0)+ Nz([7B],0)+ Nz([7C],0)+ Nz([7D],0)+ Nz([7E],0)+ Nz([7F],0)+ Nz([7G],0)+ Nz([7H],0)+ Nz([7I],0)+ Nz([7J],0))/(Critcount))

Just want to confirm...The bit in the beginning keeps the 0 in the Nz([1A],0 from making blank values equal to 0, right?

[Missinglinq]

...The bit in the beginning keeps the 0 in the Nz([1A],0 from making blank values equal to 0, right...

Incorrect! The NZ() does make blank Values equal to 0, but just when they're used in that calculation. It doesn't permanently change a blank Field to Zero!

Is that what you meant?

Linq ;0)>

[fateddarkness]

Thanks again for helping me. First, could you tell me if the input is correct and tell me where I should be putting it? I'm afraid I'm not having much luck. Either I get a #Name? error or Access demands I place an equal sign in the front. I don't mean to be needy, but I've only been studying and using access for two weeks and I am not proficient enough to read between the lines.

And no, I wasn't asking if it permanently changed the blank field. I believe the code you gave me is to ensure blanks prevent an error, but I was just confirming that Nz's 'value_if_null' being 0 doesn't affect the overall score. If it does mean that the value would equal zero in the calculation, I can't use it because because I do not want the blank values to count towards the average.

To be clear, blanks should be excluded, not treated at 0.

[June7]

Linq, will calculation trigger as each score is entered?

Also not seeing the procedure declaration line. Is this supposed to be a Sub or a Function?

[Missinglinq]

...Access demands I place an equal sign in the front...

If Access is demanding an equal sign in front you must be trying to do this in the Control Source for you totals Control. You can't do that, directly, because you have to run code to determine how many of the individual scores to use when you divide.

...I believe the code you gave me is to ensure blanks prevent an error, but I was just confirming that Nz's 'value_if_null' being 0 doesn't affect the overall score...

No, it won't effect the overall score.

June7

It needs to go into whatever event is used to trigger the calculation. And no, it shouldn't trigger after every value is entered.

Linq ;0)>

[June7]

OP wants to see the calc updated as each score is entered. This requires the expression referencing all the controls to be in textbox on form, not in the VBA procedure. Or must call the procedure from the AfterUpdate event of each textbox.

The functions referenced in posted link will take any number of terms and calculate with them. They will handle nulls. The expression in textbox calling one of those functions would be:

= RAvg([1A], [1B], [1C], [1D], [1E], [1F], [1G], [1H], [1I], [1J], [2A], [2B], [2C], [2D], [3A], [3B], [3C], [3D], [3E], [3F], [3G], [4A], [5A], [5B], [5C], [5D], [5E], [5F], [5G], [5H], [6A], [6B], [6C], [7A], [7B], [7C], [7D], [7E], [7F], [7G], [7H], [7I], [7J])

but it seems like you're saying each separate criterion would need its own record attached to the name of the employee, rather than all in one row

Yes, that is what I mean by 'normalized' structure. Then the intrinsic aggregate Avg function could be used.

[Missinglinq]

...OP wants to see the calc updated as each score is entered...

Sorry, I missed that. Yes, the calculation would need to be in the AfterUpdate of each Control or, given the length, in a Function referenced from each AfterUpdate.

Linq ;0)>

[June7]

It is a long list of controls but think will be within length limit of ControlSource by using the custom functions I referenced and as described in previous post.