calculated field question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm designing a database for a construction company in which there are 21
separate fields each containing a price. I would like to be able to
calculate the average of these values for each separate record. However the
help does not cover how to perform this calculation for a single record.

Is there a means to calculate the average of multiple fields in a single
record?
 
calculated said:
I'm designing a database for a construction company in which there
are 21 separate fields each containing a price. I would like to be
able to calculate the average of these values for each separate
record. However the help does not cover how to perform this
calculation for a single record.

Is there a means to calculate the average of multiple fields in a
single record?
Click to expand...

The reason you're struggling is that all of the aggregate functions are
designed to work across rows not across columns. Instead of a table with...

SomeField Price1 Price2 Price3 etc..

....you should have two tables with a 1 to many relationship so that each
price is then on its own ROW in the second table instead of in its own
COLUMN. What you're building now is a great design for a spreadsheet, but
not for a database.
 
That said, if you are absolutely stuck with the design you can use a function to
do the calculation. If you aren't stuck with the design then you are much
better off following Rick Brandt's suggestion.

Paste the following into a module and then save the module as modMathFunctions

'========================START CODE==================================
Public Function GetSum(ParamArray Values())
'John Spencer UMBC CHPDM (e-mail address removed)
'Last Update: June 14, 2001
'Calculates the sum of a group of values passed to it.
'Converts text to numeric values where possible
'Sample call: mySum = GetSum("1","TEST","2", "3",4,5,6,0)
'returns 21
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
GetSum = dblSum 'At least one number in the group of values
Else
GetSum = Null 'No number in the group of values
End If

End Function
'========================END CODE==================================

In your query, use a calculated field

Field: RecordSum: GetSum([Field1],[Field2],...,[Field21])
 
Back
Top