Average data in a field and print it out in another

[rscott1989]

I am trying take all the data from one field "TOTAL" and Average it out and print it in one of my forms. I would also want this data to update automatically as the totals are updated. I am attaching some screen shots of what I am talking about

The first pic is the totals field that i want to have averaged up. The second pic is where I want it to be printed out.

Any ideas???

Thanks so much!!

 

[AlexHedley]

Have you tried adding the AVG as a calculated field in a Query with the other data you require.
If you update a Field that the AVG uses it will update.

Aggregate Queries
http://www.599cd.com/tips/access/aggregate-query/?key=AlexForum

 

[rscott1989]

YOU ROCK! Thank you

 

[rscott1989]

Ok now how do i move the data from my new query to the display on the form i want it to?

 

[AlexHedley]

You can either set the source of the Form to be the Query that has the values in or use a DLOOKUP to display it on the Form.

 

[rscott1989]

I have tried the Dlookup, I keep getting errors. maybe im not doing it right

AvgOfTOTAL is my field name
TOTALQRY is my query name
Table1 is my table name

I want to print the number given in the "AvgOfTOTAL" field in the text box on my form.

I appreciate your patience, as i am still quiet new to this!

 

[AlexHedley]

You just need the Field and the Query name:
Set the textbox to be

=DLOOKUP ("Field", "Query")
=DLOOKUP ("AvgOfTOTAL", "TOTALQRY")


A few DLOOKUP examples:

http://www.599cd.com/tips/access/dlookup-function/?key=AlexForum

=DLOOKUP (Field, Domain, Criteria)

=DLOOKUP ("LastName", "CustomerT", "CustomerID=5")

=DLOOKUP ("Address", "CustomerT", "CustomerID=" & CustomerCombo)

http://www.599cd.com/tips/access/130724-dlookup-no-programming/?key=AlexForum

http://www.599cd.com/tips/access/130921-copy-last-record/?key=AlexForum

This is explained more in depth in Access Expert 10
Course:
http://www.599cd.com/site/courselist/access2013/expert/x10/?key=AlexForum