Access: Running Average

[maerts]

How do you create a running average in an unbound field in a form. For example, line 1 will just be an average of itself. Line 2 will be an average of line 1 and 2, line 3 will be an average of line 1, 2, and 3... and so on.

 

[lupins46]

More info is needed.
What are you calculating the average of?
(You can't calculate the average of an unbound textbox, as by definition it would always be the current visible value)

 

[maerts]

(I am sorry..I am not a technical user...I am using my husband's account) I have a subform that has a Price column and the next column I want to have a running average of the Price column.

 

[lupins46]

To do this you must have a unique id for each record in the subform (not necessarily unique across all records in the table) and the records must be listed in that sequence (ascending or descending).

The subform must be based on a query. It is necessary to add an extra column to the query to represent the running average.
The extra column would look something like:

MyAvgavg("[Pricefield]", "mytable", "mytable.mylinkfield = & mylinkfield & " AND mytable.uniqueidfield <= " & uniqueidfield)

Some syntax changes may be required if any of the fields are text rather than numbers.

Then you use this extra field in the query as the source for what is currently your Unbound textbox.

 

[maerts]

I am assuming that mylinkfield is the field that links the form and the subform to each other...which is a text field not numbers. How do I adjust the syntax for this?

 

[lupins46]

MyAvgavg("[Pricefield]", "mytable", "mytable.mylinkfield = '" & mylinkfield & "' AND mytable.uniqueidfield <= " & uniqueidfield

There was a double quote missing in my last post.
Single quotes have also been added to deal with the text value.