asked on

Calcualte average on row level from multiple fields

Hi. How can I convert to access queries following formulas

=AVERAGE(B2:H2)

Is there any way to calculate average on row level (query level) for accounts

Please see table 1 with desired output and table 2 with the source data
New-Microsoft-Access-Database.accdb

als315

It is not so simple, because Access don't have group functions for fields in a record(columns). In normalized DB AVG is calculated from different records and in your DB will be better to have 7 records for each Account.
But you can do it manually with calculated fields in table or in query.
Look at sample (table2 and Query2)
In Table2 avg calculation allow Null fields
New-Microsoft-Access-Database.accdb

maximyshka

ASKER

1) I know that it is difficult and Access does not allow grouping. Therefore, I asked this question.
2) I also was thinking about the approach Expr1: ([Table 2]![Value 1]+[Table 2]![Value 2]+[Table 2]![Value 3]+[Table 2]![Value 4]+[Table 2]![Value 5]+[Table 2]![Value 6]+[Table 2]![Value 7])/7

Is this the only choice we should use?

I'm only concerned with hard coded "/7". For example if one value "Value 6" or Value 7 will be missing. The average should affect missing values. We should adjust instead of 7 use 6 or

How should we adjust query in such case? Should I provide additional clarity to my question?

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

als315

maximyshka: look at calculated field (AVG) in my sample (Table2), there is no hard coded "/7" and you can use same formula in query.