IF you insist on the records being structured the way they are then you will
need to use a VBA function to get your average or a Union query to normalize
the data
SELECT Location, "Jan" as MonthName, [Jan_06] as MonthValue
FROM [Your Table]
UNION ALL
SELECT Location, "Feb" as MonthName, [Feb_06] as MonthValue
FROM [Your Table]
....
UNION ALL
SELECT Location, "Dec" as MonthName, [JDEc_06] as MonthValue
FROM [Your Table]
Save that as qFixedData THen use that in a totals query.
SELECT Location, Avg(MonthValue) as TheAvg
FROM qFixedData
GROUP BY Location
If you decide to do it with VBA:
Here is a function that should work for you. Paste it into aVBA module and
save the module (as modDeNormFunctions)
You can call it in a query (maximum of 29 fields allowed) as follows.
Field: GetAvg: fRowAverage([Jan06],[Feb 06], ..., [Dec 06])
Public Function fRowAverage(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
'At least one number in the group of values
fRowAverage = dblSum / intElementCount
Else
fRowAverage = Null 'No number in the group of values
End If
End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Michel Walsh said:
A database is built, design, with operation across records, not across
fields, in mind. That is somehow also linked to the "normalization rules".
Sure, you can use something like:
iif(0 = field <> 0 + field2 <> 0 + field3 <> 0 + ... + fieldN<> 0,
"infinity",
(field1 + field2 + field3 + ..... + fieldN) / ABS(field <> 0 +
field2 <> 0 + field3 <> 0 + ... + fieldN<> 0))
nut that is definitively more typing involved, as you can see.
That works because a comparison evaluates to true, -1, or to false, 0 (or
to NULL, but I assumed you don't have null values). So, the divisor will
be a sum of -1 and of 0. Taking the absolute value gives us back the
number of time the fields are not zero.
Hoping it may help,
Vanderghast, Access MVP
skigal said:
Also, which functions can I use over fields not records? Maybe count
isn't
one of them I can use anyway?
Michel Walsh said:
AVG do exists, but works vertically, across the record, not
horizontally,
across the fields... So, if all the months have a value, indeed, you
have
to do the sum ... manually, as suggested.
Hoping it may help,
Vanderghast, Access MVP
I don't know whether AVG exists.
Why not
Average: (([Jan 06]+[Feb 06]+[Mar 06] etc)/12) ???
You may not need the outer brackets
:
I have a simple table consisting of 13 columns. The first col is the
location and the next 12 are the scores received in each of 12
months,
some
months have a score of zero. I am trying to do a query that will
average
these 12 scores.
The formula is AVG([Jan 06]+[Feb 06]+[Mar 06] etc).
I have tried the formula with commas seperating the argument and it
still
errors out. I have also tried changing the field names to Jan_06 to
eliminate spaces and that didn't work.
Error message is either "You tried to execute a query that does not
include
the specified expression 'Location' as part of the aggregate
function" or
"The expression you entered has a function containing the wrong
number of
arguments" depending on if I use the + or the , to seperate the
arguments.
How do I get the average function to work?
