Calculating average within one record

[Bruce Hartsell]

I'm trying to calculate an average of three fields within one record, without
success.
The three fields are Score1, Score2, and Score3.

My expression is Expr1: Avg([Score1]+[Score2]+[Score3])
Access returns an error message saying "You tried to execute a query that
does not include the specified expression 'Names_NamesID' as part of an
aggregate function.

The query draws data from two tables, Names and Addresses.

Any ideas how to do what I'm trying to do? I do not do Visual Basic or SQL.

Thanks,
Bruce Hartsell
Department of Social Work
California State University
Bakersfield, CA

 

[MGFoster]

I'm trying to calculate an average of three fields within one record, without
success.
The three fields are Score1, Score2, and Score3.

My expression is Expr1: Avg([Score1]+[Score2]+[Score3])
Access returns an error message saying "You tried to execute a query that
does not include the specified expression 'Names_NamesID' as part of an
aggregate function.

The query draws data from two tables, Names and Addresses.

Any ideas how to do what I'm trying to do? I do not do Visual Basic or SQL.

Expr1: (Nz(Score1,0)+Nz(Score2)+Nz(Score3))/3

 

[John Spencer (MVP)]

To ignore nulls (blanks) when calculating this average.

Expr1: (Nz(Score1,0)+Nz(Score2)+Nz(Score3))/(Abs(IsNumeric(Score1)+IsNumeric(Score2)+IsNumeric(Score3)))

I'm trying to calculate an average of three fields within one record, without
success.
The three fields are Score1, Score2, and Score3.

My expression is Expr1: Avg([Score1]+[Score2]+[Score3])
Access returns an error message saying "You tried to execute a query that
does not include the specified expression 'Names_NamesID' as part of an
aggregate function.

The query draws data from two tables, Names and Addresses.

Any ideas how to do what I'm trying to do? I do not do Visual Basic or SQL.

Expr1: (Nz(Score1,0)+Nz(Score2)+Nz(Score3))/3

 

[Duane Hookom]

You can also normalize this "spreadsheet" design with a union query and run
a totals query based on the union query.

--
Duane Hookom
MS Access MVP

To ignore nulls (blanks) when calculating this average.

Expr1:
(Nz(Score1,0)+Nz(Score2)+Nz(Score3))/(Abs(IsNumeric(Score1)+IsNumeric(Score2)+IsNumeric(Score3)))

I'm trying to calculate an average of three fields within one record,
without
success.
The three fields are Score1, Score2, and Score3.

My expression is Expr1: Avg([Score1]+[Score2]+[Score3])
Access returns an error message saying "You tried to execute a query
that
does not include the specified expression 'Names_NamesID' as part of
an
aggregate function.

The query draws data from two tables, Names and Addresses.

Any ideas how to do what I'm trying to do? I do not do Visual Basic or
SQL.

Expr1: (Nz(Score1,0)+Nz(Score2)+Nz(Score3))/3