Question Average

[aakil]

Hi,
Im fairly new to access and need help with my form.

=([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D])

These are the categories that have a numeric rating of 0-6.

I would like to get the average of the categories even when someone chooses zero.

as you can see i had the categories adding up and then dividing by 4, however when zero was chosen...my average was wrong....ANY HELP PLEASE.....im a real novice so if there's a simple solution that would help ...Thanks!!

 

[recyan]

=([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D])

These are the categories that have a numeric rating of 0-6.

I would like to get the average of the categories even when someone chooses zero.

as you can see i had the categories adding up and then dividing by 4, however when zero was chosen...my average was wrong....ANY HELP

check if below gives some guidelines :

Let us say :
TotalSum : ([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D]);

TheAverage : iif(TotalSum=0, 0, TotalSum/4)

Thanks

 

[aakil]

Hi,
Thanks...I still got an error message???

 

[plog]

There's no reason to test to see if the numerator is 0 because 0/4 evaluates to 0.
That shouldn't cause an issue.

What does the error message say exactly and what is the exact code underlying your calculation?

 

[aakil]

=([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D])/4

is exactly what I have in the control source....which gives me an average, yet because I'm saying divide by 4 when I attempt to get an avg it's off....for instance if the scores are 0, 2, 5, 5 respectively, I get an average of 3....because it's doing 12/4....but since the zero means nothing...I would like it to do 12/3 which is 4???

Thanks!!

 

[aakil]

let me ask another question....for the categories I listed....does it matter if the box is a drop down vs a standard one?

 

[plog]

If you know what the valid responses are for a field, then you should use a drop down---never trust users nor their data.

To accomplish what you want, you should use this:

=IIf(([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D])=0, 0,([Physical Support of the Child_D]+[Emotional Support of the Child_D]+[Participation and Engagement_D]+[Progress to Safe Case Closure_D]) / (iif([Physical Support of the Child_D]>0, 1,0) + iif([Emotional Support of the Child_D]>0, 1,0) + iif([Participation and Engagement_D]>0, 1,0) + iif([Progress to Safe Case Closure_D]>0, 1,0)))

I wrote the above without testing it, so hopefully it works. In actuality, its so complicated, it would probably be better to create it as a function in a module.

 

[aakil]

Hi....yes the field now displays------ #Name?

I put the code in as a control source???

 

[plog]

Ok, let's turn it into a function.

On your form change it to this:

=getAverage([Physical Support of the Child_D],[Emotional Support of the Child_D],[Participation and Engagement_D],[Progress to Safe Case Closure_D])

Then create a new module and put this code in it:

Function getAverage(n1, n2, n3, n4)
    ' determines average of n values, excluding any that are 0
ret = n1 + n2 + n3 + n4
    ' return value, set to sum of all n values
If (ret > 0) Then
    ' if sum of all n values is greater than 0, calculates denominator and divides ret by it
    denom = 0
    If (n1 > 0) Then denom = denom + 1
    If (n2 > 0) Then denom = denom + 1
    If (n3 > 0) Then denom = denom + 1
    If (n4 > 0) Then denom = denom + 1
    ret = ret / denom
    End If
 
getAverage = ret
End Function

Go back to your form and it should work.

 

[aakil]

Thanks....it still didn't work, I am not sure if I did anything wrong...I copied and put everything in exactly as you said.....would you mind if I mailed you the database, maybe I have something set up in my tables incorrectly or something?

 

[plog]

Sure, I sent you a message with my email.

 

[plog]

You didn't reference the right fields. You need to change the field on the form to this:

=getAverage([Physical Support of the Child_CG],[Emotional Support of the Child_CG],[Participation and Engagement_CG],[Progress to Safe Case Closure_CG])

 

[aakil]

ok...so would I need to make a code for each indicator? I thought I would be able to take the code and have it apply under each indicator in the overall score box??

Thanks!!

 

[plog]

You could do that, if you rewrite the getAverage Function more generically.

Your initial post stated you had 4 values you wanted to get an 'average' for, so I wrote it to do just that. Now you want to use that function for an undetermined number of values. To make that function work like that you will need to reconfigure it to accept an unknown amount of values.

On your form you can still use:

=getAverage([a], , [c], ...)

But you will have to reconfigure the getAverage function.

 

[aakil]

So I figured out the issue.....
On my form I have several categories that you can choose a number rating for.

For example: (Record 1) Permanency Prospects 5, Safety 4, Stability 3 = 12
(Record 2) PP 6, Safety 5, Stability 4 = 15

How do i get totals of the category of just the record.....not all of the instances.

From the above example, if I ask for Total of PP+Safety+Stability the total I get is 27 and not 12 or 15 as i would like for it to do

Is this even possible or should I restructure how I capture the ratings?

 

[plog]

You've lost me, your forms are configured to show only 1 record at a time. Also,
PP+Safety+Stability should add up to whatever their individual values add up to.


It seems like this doesn't have anything to do with your initial post. Again, lost.

 

[GaryPanic]

quick cheat
have a table set up with the value you want like this
two columns
0-0
1-1
2-1
3-1
4-1
5-1
6-1

so when x picks a number it will have two values
the first being your choice 0-6 and the other being the number to divid it by
do the same for all of your drop downs or you can code it at the end after update of each field or the last one you will have a number on the left hand side (assuming 0-6 - so max would be 24 on four fiields and the max on the other side would be 4 - x/y = average
if you select a 0 on the left hand side you will get a 0 on her right hand side it will remove itself from x/y
its a simple get round

 

[GaryPanic]

have a look at a very simple db
1 table 2 values
then look atthe form and see what changing the values does - add as many as you like -

 

[GaryPanic]

So I figured out the issue.....
On my form I have several categories that you can choose a number rating for.

For example: (Record 1) Permanency Prospects 5, Safety 4, Stability 3 = 12
(Record 2) PP 6, Safety 5, Stability 4 = 15

How do i get totals of the category of just the record.....not all of the instances.

From the above example, if I ask for Total of PP+Safety+Stability the total I get is 27 and not 12 or 15 as i would like for it to do

Is this even possible or should I restructure how I capture the ratings?

is this on a form or in a report - you need to seperate out the record source
by the use of either Me. in the correct place
so if its on a form me. mean this record -without it you re going to be all over the place (same applies with reports - the source however would be record 1- usually along the lines of the underlying qry being forms!XXFormnameXX! xxfieldnameXX - this should seperate out your values - or you could try group

I am going to asseme its on a form - in your values in code when you ahve a+b+c+d put the following
me.a + me.b + me.c + me.d


me. refernces it to the the form you are on