Running Average/Calculation on a Form

P

PPCO

I have a form where we enter production records. We enter Date, Quantity, Man
Hours, Item Name, Etc. Just wondering if it's possible to create a calculated
field on the form that would show what the production average for the past
month is and a field that shows how the current entry affects that average.
Basically, it would be nice if they could see a running average as they enter
the production. I'm sure I can do that in a report but not sure on a form.
Thanks
 
T

Tom van Stiphout

On Wed, 24 Sep 2008 16:14:01 -0700, PPCO

Running sums (but likely averages as well) are best supported in
reports. I would have to scratch my head on how to do it in a form.
Would need to know more information, and likely some funding as well
:)
A report isn't bad, and only one mouseclick away.
Perhaps the DAvg function would work for you?

-Tom.
Microsoft Access MVP
 
T

Tom Wickerath

Hi PPCO,

Are you looking for a running average, or a cumulative average, using the
definitions provided in this Wikipedia article?

http://en.wikipedia.org/wiki/Running_average

If you are looking for a cumulative average, I think you can achieve that by
using the domain aggregrate function DAvg, with the proper criteria to
identify the appropriate set of records. I put together a sample that
calculates the cumulate average temperature for a given month, along with the
average temperature for the entire month. This is done using VBA code in the
Form_Current procedure. (I started by having the same calculations entered as
the control source for the text boxes, but moved to the VBA method to prevent
a #Error when one navigated to a new record). This sample is available here:

http://www.accessmvp.com/TWickerath/downloads/DAvgTest.zip


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
P

PPCO

That's what I thought, but had to ask. Is there a macro that would
automatically fill in a date range on a report for the past 6 months? Thanks
 
P

PPCO

Thanks for the info--I am looking for a running average for like the previous
6 months on the form if possible. On the form, I am entering the Product
Name, Man Hours, Process, Quantity. The Average function in the control on
the form would somehow have to match those factors before pulling up an
average. Can I get it to pull up a running average for the past 6 months on
the form after the data record has been entered? I know I can do this on a
report...if the report is the only way, is there a macro I can run that would
automatically enter the date range as the previous 6 months for the report?
Thanks!
 
P

PPCO

Looks like exactly what I need it to do...how in the world did you do that?
Would I be able to get it to work with several criteria as mentioned
previously?
 
P

PPCO

Probably don't have to--I agree a report would be much easier. If I were to
try it on the form, would I be able to write an event procedure in a query to
get this to work (I am limited on SQL)? Thanks
 
T

Tom Wickerath

I don't understand what you mean by "...write an event procedure in a
query...". What I was suggesting earlier is that you add additional criteria
to your existing DAvg function. This web site shows using DLookup:

General: DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

To add additional criteria, you would do something like what is shown in the
"Mix-n-Match" section.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CURRENT Average problem 3
Calculate Average In Report? 1
Date formatting and calculation 2
calculated field - weighted average 3
Calculating averages on a form 3
Average calculations 3
Average Calculation 4
Average on a series of fields 3

Top