Average data in a field and print it out in another (1 Viewer)

rscott1989 · Oct 28, 2013

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 · Oct 28, 2013

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

Pat Hartman · Oct 28, 2013

The table you posted isn't normalized. It is probably the view used for reporting and hopefully, the underlying data is normalized. In Excel where sheets have both width and length, tables have only length so there are no functions that will aggregate "across" columns. All aggregation is done on sets of data and that equates to rows.

rscott1989 · Oct 28, 2013

YOU ROCK! Thank you

rscott1989 · Oct 28, 2013

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

AlexHedley · Oct 28, 2013

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 · Oct 29, 2013

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 · Oct 29, 2013

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

Average data in a field and print it out in another (1 Viewer)

rscott1989

Registered User.

Attachments

AlexHedley

Registered User.

Pat Hartman

Super Moderator

rscott1989

Registered User.

rscott1989

Registered User.

AlexHedley

Registered User.

rscott1989

Registered User.

Attachments

AlexHedley

Registered User.

Similar threads

Users who are viewing this thread