Sum Multiple Fields

theSizz · Jan 13, 2007

I have a table named tblItems.Each record has multiple fields named It1,It2,It3,It4,etc. etc. up to It25.These fields are numeric type.I am placing a calculated control on a form that will give me the sum of these 25 different fields for each record. My question is what's the most efficient way to write the formula in the calculated control's property. I assume there must be an easier way than =[It1]+[It2]+[It3]....+[It25]

Thanks for the help.

PHV · Jan 13, 2007

Your table seems not normalised.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

MichaelRed · Jan 13, 2007

search for "basSUM" or "basRowSum" in these (Tek-Tips) fora

In the general sense 9b]PHV[/b] is probably correct, however it is occassionally necessary to perform aggregate functions on (parts of) records as opposed to fields of a recordset. basSUM (and its cousins) illustrate a reasonable approach to this. in many, most instances, the row aggregates are best applied from a query

MichaelRed

theSizz · Jan 13, 2007

Thanks MichaelRed. Here's what I did. Inserted your code in a module.

Code:

Public Function basSum(ParamArray varMyVals()) As Variant

    'Michael Red 3/7/2002
    'To return the SUM of a series of values

    'Sample Usage:
    '? bassum(1, 2.0067, 3)
    '6.0067

    Dim Idx As Integer
    Dim MySum As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (IsNumeric(varMyVals(Idx))) Then
            MySum = MySum + varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basSum = MySum

End Function

Next in a query I added:

TOT: basSum([cal1],[cal2],[cal3],[cal4],[cal5],[cal6],[cal7],[cal8],[cal9],[cal10],[cal11],[cal12],[cal13],[cal14],[cal15)

This produces the desired results. However, I would like to know if this is the most efficient way to write the syntax in the query i.e. cal1,cal2,cal3,.....cal15.

Since there are other numeric fields in the record,I didn't use your other code basRowSum which will sum and include ANY and ALL numeric fields, including "Date", "Currency", etc. I only want to sum the above group of fields.

Thanks I appreciate the help.

MichaelRed · Jan 13, 2007

Efficiency", here, is somewhat difficult to quantify. Queries are "ALWASY" faster than procedural code. On hte other hand, queries which access procedural code are necessarily slowed by that usage. It is not clear to me that the procedures have the same penalty as they would in code behind forms. it is not hard to see the penalty in some instances, as you could time the operations in the various combinations, such as placing the procedural code in the query (as you have done) - with some code to 'time' the operation. Then simply remove the calculation from the query and place in in the control source, again timing hte results.

Of course, then there is the issur=e of the form properties, specifically the continious vs single. It SEEMS like the single form would handle the process more efficiently with the procedure in the form control, but then how the user navigates through the records may have an impact on that as wekk,

The more general thought is that the hardware of todays desktop systems is usually more constrained by I/O than any internal calculatios, so it doesn't even make sense to care unless you are working with truly HUGE recrdsets, and then you shouldn', be using the venerable (if kind hearted) Ms. A's services any way (at least not with the JET db engine).

MichaelRed

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Sum Multiple Fields

theSizz

Technical User

PHV

MIS

MichaelRed

Programmer

theSizz

Technical User

MichaelRed

Programmer

Similar threads

Part and Inventory Search

Sponsor