Show average in form based on query

[ahmed_optom]

I want to display some average data on an input form. The average data is based on a query from the same table as the form.

I can make the query, it generates the average, I can display this on my form, but now I can save the recordset, its giving the error the recordset is not updateable.

Is there a simple way of showing an average for a field from all previous records?

 

[plog]

Forms that interact with data (add/edit/delete) should be based on tables. It sounds as if your form is not based on a table but a query. You should make your form based on your table.

When you need to display data on a form like that, you create a query to generate that data, but then retrieve the data from the query by using a Dlookup on the form. So, the control source of the input that displays that average should be a Dlookup into your query.

 

[ahmed_optom]

Thanks plog, after the usual fun and games of dlookup synax, I got it working.

Just for others, you have to note that the dlookup of the average is not the same as the field in the design view. When you go to datasheet view, you will see that it has added AvgOf at the front, and that is what needs to be referenced in the dlookup. Seems obvious now, but took me a while to figure out.

 

[plog]

You could also use DAvg and not use a query at all.

 

[The_Doc_Man]

its giving the error the recordset is not updateable.

This is actually quite common if the recordset contains an SQL aggregate such as SUM or AVERAGE. The reason is that the records in the set potentially represent multiple input records that are aggregated into a single record (to form the average). Which means that if you tried to do an UPDATE through that recordset, Access and the DB engine don't know which record to update in the original table - because a single record from that table isn't being presented. And, by implication, the other fields are stand-ins for the fields used for the WHERE or GROUP BY clauses. They are not raw data but exemplars of the original raw data.