Total rows in a query

  • Thread starter Thread starter Michael Beatty
  • Start date Start date
M

Michael Beatty

I'm trying to total all the rows in an access query, my problem is that if
there is a null value in any of the columns the total dosn't show up. I
then (state2) want to be able to devide that total to get an average. Is
there a way to count the number of columns that have data in it so I can get
an average
 
You need to provide some sample records since summing a column/field of
numeric values (even when some may be null) will always result in the sum of
the values. My guess is that you are adding across fields which suggests
un-normalized tables.
 
Hopefully, answered in your earlier thread.

QUOTE

To do this in a row, you need to do one of two things. The best is to normalize
your data, but you can use a function to do this.

Paste the function below into a module and then call it from your query.

Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'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
fGetMeanAverage = dblSum / intElementCount 'At least one number in the
group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

Another method would be to make a union all query and then use that as the
source of a totals query.

SELECT PrimaryKeyField, ValueFieldA
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, ValueFieldB
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, ValueFieldC
FROM YourTable
....

Then

SELECT PrimaryKeyField, Avg(ValueFieldA)
FROM SavedUnionQuery
GROUP BY PrimaryKeyField

END QUOTE
 
I am looking for the same expression, or method. Good luck
Seems like a simple thing, but...
 
Back
Top