Sum of 18 Fields 1

[tpearo]

I have a form based on a table that has multiple fields.
I need to get a total of several of the fields and display this total on the form. Then I need to get an average and display this average based upon only the fields that have data in them. For example there are 18 fields but each record sometimes only has data in 10 of the fields. Other times it might have data in all 18 fields.
I hope I'm being clear enough.
Any help would be appreciated.

Thanks in advance,

Tp

 

[Remou]

What is in the fields that have no data? That is, null, zero length string or zero?

 

[tpearo]

It is null or another words there will be cases where there is no data entered in those fields.

Tp

 

[Remou]

You could try setting the Tag property for each of the 18 fields to say, "AddThis". Then you could use a little code, perhaps:

Private Sub Form_Current()
Dim ctl As Control
Dim dblSum As Double
Dim lngCount As Long
Dim dblAverage As Double

For Each ctl In Me.Controls
    If ctl.Tag = "AddThis" Then
        If Not IsNull(ctl) Then
            dblSum = dblSum + ctl
            lngCount = lngCount + 1
        End If
    End If
Next
Me.txtTotal = dblSum
Me.txtCount = lngCount
Me.txtAverage = dblSum / lngCount
End Sub

 

[MichaelRed]

search these fora key words basSumVal and / or basAvg.


the tag thing is a property of controls on forms and not applicable to recordset fields.

an alternative would be to do the 'two step". first, a crosstab query to normalize the structure and then an aggregate query to get the sum & avg calcs.




MichaelRed

 

[Remou]

MichaelRed
Thank you for the above. Unfortunately, a search for basAvg produces only Average function, thread703-813804; basAvgVal does better, producing Query: Calculate Total Per Record and Finds Highest, thread701-635591, and further recommends Determine which variable has lowest value, thread705-590307, which shows basMaxVal.

 

[MichaelRed]

At my age/memory status, probably hte best I can hope for. The generic set of "row-set" (or parameter array based) functions were an attempt to satisfy those with a distaste for the more esoteric query operations (e.g. crosstab / piviot table). I usually attempt to reference at least one, with the (usually vain) hope that most anyone dabbling in techno stuff can easily transmorgify the simply stastical / aggregates. After all, how much difference is there in the calculation of Min, Max, Sum and Avg? Each simply goes throught the set of values and picks out some value(s) and optionally treats the aggregate to some minor transform.




MichaelRed