Compute the Average with Null Value 3

[Tamrak]

Hello,

I am setting up the database that contains the following fields:

A1, A2, A3, B1, B2, B3, C1, C2, and C3. All fields are set up numeric which allowed the inputs of 1, zero or null.

The A set will go together. So does the B set and the C set. I need to set up a query that will compute an average for the A set, B set and C set. So, I created three calculated fields, AVGA, AVGB and AVGC.

Remember, the null value is in there. The field is set up as numeric/byte.


These are some of my data, 4 records, 4 lines.

A1 A2 A3 B1 B2 B3 C1 C2 C3

1 0 - - - - 0 1 0
- - - 0 1 - - - -
- - - - - - - - -
1 0 - - - 0 - - -

The dash will be null(blank) in the working table. For illustration purpose, I need to put the blank in here. Otherwise, you might not be able to see it. The display might shift a little bit due to indentation.

These are what I would like to accomplish:

1. I need to average A, B, C per record by putting the results in the AVGA, AVGB and AVGC. As you see, some of the records contain totally null value. The average null value will result as blank. For example, in record # 3.

2. After I receive the result from AVGA, AVGB and AVGC, I need to average these three calculated fields into one final calculated field which is AVGABC. Some of the AVGA or AVGB might be null which should not be calculated in the final result.

I would like to design a query that will provide me the result based on the above criteria. I use a query Wizard from Access. Shall I set the property differently? I do not know much about the SQL.

Any helps toward this question will be very much appreciated. If I am not clear in this example, please e-mail me.

Thank you again for your time. Have a great Labor Day weekend.


P.S. If you recommend me to construct AVGA, AVGB, AVGC and AVGABC permanently in the table, that will be fine as well. However, I need to know how to update each record when the data entry is completed. Thank you.

 

[tlbroadbent]

I suggest that you create a function to calculate the averages. You can start with the following function and modify as needed. Open a new module and paste this code into it.

Function CalculateAvg(val1, val2, val3)
Dim TotVal As Single, TotCnt As Integer
TotVal = 0
If Not IsNull(val1) Then
TotVal = TotVal + CSng(val1)
TotCnt = TotCnt + 1
End If
If Not IsNull(val2) Then
TotVal = TotVal + CSng(val2)
TotCnt = TotCnt + 1
End If
If Not IsNull(val3) Then
TotVal = TotVal + CSng(val3)
TotCnt = TotCnt + 1
End If
If TotCnt > 0 Then
CalculateAvg = TotVal / TotCnt
Else
CalculateAvg = Null
End If
End Function

Call the function from a query like the following to calculate averages for each set of values in the table.

SELECT
tbl.RecID,
CalculateAvg([a1],[a2],[a3]) AS AvgA,
CalculateAvg([b1],[b2],[b3]) AS AvgB,
CalculateAvg([c1],[c2],[c3]) AS AvgC,
CalculateAvg([AvgA],[AvgB],[AvgC]) AS AvgABC
FROM tbl;

Let me know if you have any questions. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MichaelRed]

It is not my intent to disagree w/ Terry. I JUST happen to be reviewing some old notes re the use of assignment instead of logical constructs TODAY. I am more-or-less totally uncertain of the actual desired results, however the following accomplishes the same functionallity as Terry's without the use of quite so many conditional statements. In the normal course of events, the overall function is much to trivial to be concerned over the difference in execution time, and my use of the array probably offsets most of the difference anyway, so this offering is simply presented as the alternate approach. Some (darn few) will find it easier to understand, some (quite a lot) will find it more difficult.

Public Function basAvgThree(Val1 As Variant, _
                            Val2 As Variant, _
                            Val3 As Variant) As Variant

    Dim MyVal(3, 2) As Single

    MyVal(1, 1) = Nz(Val1)
    MyVal(2, 1) = Nz(Val2)
    MyVal(3, 1) = Nz(Val3)
    MyVal(1, 2) = Not IsNull(Val1)
    MyVal(2, 2) = Not IsNull(Val2)
    MyVal(3, 2) = Not IsNull(Val3)
    

    MyVal(0, 1) = MyVal(1, 1) + MyVal(2, 1) + MyVal(3, 1)
    MyVal(0, 2) = Abs(MyVal(1, 2) + MyVal(2, 2) + MyVal(3, 2))

    If (MyVal(0, 2) > 0) Then
        basAvgThree = MyVal(0, 1) / MyVal(0, 2)
    End If

End Function

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Hello,

Thank you for your reply and your time.
Let me step through a little bit deeper because the reply is not quite clear.

First, I created a query that has all of the described fields. I also created four calculated fields in the query. If I run the query, all of the fields, including the calculated fields must provide either the numbers or not calculate at all. From the previous example, the results will show something like:


A1 A2 A3 AvgA B1 B2 B3 AvgB C1 C2 C3 AvgC AABC

1 0 - .5 - - - - 0 1 0 .33 .4
- - - - 0 1 - .5 - - - - .5
- - - - - - - - - - - - -
1 0 - .5 - - 0 0 - - - - .33


I do not know where to put an SQL statement at. Do I set it at the properties? Please advise. Thank you.

 

[MichaelRed]

This is the result. It should be the SAME wheather you use Terry's function or mine. The QUERY shown in Terry's response works for either, except for the differnet name of the function. You can just copy either / both functions to a PUBLIC module. To use the Query, copy the text of the statement from Terry's post and paste it into the SQL view of an "empty" query. If you are using Terry's function, just click the table view of the query. If you are truing to use my version, replace all occurances of "CalculateAvg" with "basAvgThree" and THEN click on the "table view" of the query.

A1  A2  A3  B1  B2  B3  C1  C2  C3  AvgA  AvgB  AvgC AvgABC
1   0	                0   1   0   0.5	  0.33       0.41
            0   1                         0.5        0.5

1   0               0               0.5   0          0.25
[/code MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Hello Mike,

Thank you for your response in the Tek-tips. I tried to load your Module and it still does not work. The error was ->"Wrong Number Arguments Used with Function in Query Expression ->"basAvgSeven([A1],[A2],[A3])" I use
this argument to test only in one series to see whether it works.

I can send you an Access 2000 file to review. Can you help us? The sample file is approximately 152 KB, very small. There is one table, one query and a module. You might be able to attach the query back because it is not working at my end. My table structure is simple. The fields are numeric/byte/fixed. There are only 50 records in there.

Please provide a valid e-mail. I wrote a short response and the e-mail came back "undeliverable."

Thank you.

 

[MichaelRed]

Hmmmmmmmmmm,

Several issues here.

"basAvgSeven" implies some change in the overall scheme of things. The module I sent was "basAvgThree", so at the VERY least the name has changed. DId you COPY the code from the thread, or rekey it? Why is the name Changed? WHAT is the e-mail address you sent the info to?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Mike,

Thank you for reply back soon.

We changed the function name to match an actual series that we have. Instead of three, we have seven of them, from A to G. I believe that it will provide the similar example that we can adapt it later. We increased the val3 to val4,5,6 and 7.

I sent it to your e-mail at att.net. It might not work.

********************************************************
Sent >>> RCPT TO:<mred@att.net>
Received <<< 550 Invalid recipient: <mred@att.net>

Could not deliver mail to this user.
***************** End of message ***************

Please reply. Thanks.

 

[MichaelRed]

m.red@att.net

If you changed the overall process to accomodate SEVEN items, you need to SEND sevem ITEMS.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

Thanks for finding the error in the e-mail. Not many use it, but having the error is worisome. what if someone wanted to offer me a JOB?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

In a seperate e-mail to me, Tamrak indicated that the &quot;actual&quot; function needed to accomodate UP to seven seperate fields. By &quot;Inspection&quot;, it was determined that the MIN number (in the specifc instance) was four. I modified the procedure to make val4 through val7 optional parameters to the procedure and added checks for the values bein missing from the call list.

Tamark had already expanded the array and added the calculation with the additional &quot;Vals&quot; and the count of the vals in the average.

The revised procedure is posted:

Public Function basAvgSeven(val1 As Variant, _
                            val2 As Variant, _
                            val3 As Variant, _
                            Optional val4 As Variant, _
                            Optional val5 As Variant, _
                            Optional val6 As Variant, _
                            Optional val7 As Variant) As Variant

    Dim MyVal(7, 2) As Single

    MyVal(1, 1) = Nz(val1)
    MyVal(2, 1) = Nz(val2)
    MyVal(3, 1) = Nz(val3)
    If (Not IsMissing(val4)) Then
        MyVal(4, 1) = Nz(val4)
    End If
    If (Not IsMissing(val5)) Then
        MyVal(5, 1) = Nz(val5)
    End If
    If (Not IsMissing(val6)) Then
        MyVal(6, 1) = Nz(val6)
    End If
    If (Not IsMissing(val7)) Then
        MyVal(7, 1) = Nz(val7)
    End If
    
    MyVal(1, 2) = Not IsNull(val1)
    MyVal(2, 2) = Not IsNull(val2)
    MyVal(3, 2) = Not IsNull(val3)
    If (Not IsMissing(MyVal4)) Then
        MyVal(4, 2) = Not IsNull(val4)
    End If
    If (Not IsMissing(val5)) Then
        MyVal(5, 2) = Not IsNull(val5)
    End If
    If (Not IsMissing(val6)) Then
        MyVal(6, 2) = Not IsNull(val6)
    End If
    If (Not IsMissing(val7)) Then
        MyVal(7, 2) = Not IsNull(val7)
    End If
    
    MyVal(0, 1) = MyVal(1, 1) + MyVal(2, 1) + MyVal(3, 1) + MyVal(4, 1) + MyVal(5, 1) + MyVal(6, 1) + MyVal(7, 1)
    MyVal(0, 2) = Abs(MyVal(1, 2) + MyVal(2, 2) + MyVal(3, 2) + MyVal(4, 2) + MyVal(5, 2) + MyVal(6, 2) + MyVal(7, 2))

    If (MyVal(0, 2) > 0) Then
        basAvgSeven = MyVal(0, 1) / MyVal(0, 2)
    End If

End Function

Overall, this illustrates the difficulty in dealling with an incomplete &quot;specification&quot; rather nicely. More time effort and bandwidth has been expended on 'clarification' of the real needs than on doing any actual work.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Excellent work Michael. Worth at least one more star.

Around our office, clarification of requirements is usually the &quot;real work.&quot; The rest is comparatively easy. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[MichaelRed]

? Is there some magic number of stars which get me to at least an interview for a job? If anyone knows of such a threshold, could you give me a hint?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Mike,

You are genious! We have got the results that finally works!

One quick note that might be easy for you. I am trying to change the properties of my calculated fields (AvgA, AvgB, AvgC ...) into percentage because of too many decimal digits. I converted the queries into the report and changed the properties to percentages. It won't accept the property change.

Do I have to change anything in the VBA module to have it formatted from decimals to percentages? A final hint might be helpful. I can go from here. I do not know why I cannot change the format at the property level.

Thank you again for make this day very special day for our group.

 

[MichaelRed]

Because the function needs to be ble to return a NULL, the return value NEEDS to be a variant. To &quot;See&quot; the values as &quot;Percent&quot;, they need to be forced to some &quot;Numeric&quot; type. I would suggest using CSng(basAvgSeven([A1]. [A2], ... , [A7]))
and then set the field format to Percent.

this will be O.K. - UNTIL you actually return a NULL. then, You will probably need to Include Nz beteween the CSng and the basAvgSeven

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Tamrak]

Michael,

Thank you.

Problem is: when I used the CSng(basAvgSeven([A1]. [A2], ... , [A7])), the Average returned &quot;#Error.&quot; However, it gave me the percentage value that I am looking for.

When I used the CSng(NZ(basAvgSeven([A1]. [A2], ... , [A7])), the null value turned &quot;0.00%&quot; which affected the real calculation for all categories combined. The null value will be factored in as zero. Calculation will not omit it.

We came very far since morning. Everything works with just this minor detail. All comments are welcome.

I realize that you must be very tired, answering and replying this e-mail. I am appreciated everything that you have done today. Without your knowledge, it is impossible to start our project work.

Thank you.

 

[MichaelRed]

Incl the CSng ONLY for the average of averages (&quot;AvgABC&quot in your original post. since this is part of the query statement, it does not require any change to te procedure.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over