Running Average/Calculation on a Form

[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

 

[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

 

[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
__________________________________________

 

[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

 

[Tom Wickerath]

Hi Tom

Perhaps the DAvg function would work for you?

That's how I attacked this problem. Not sure if you or PPCO saw my earlier
reply:

http://www.microsoft.com/office/com...cess&mid=2eaf689a-ee1f-4025-aead-f7638c4e93b4


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

 

[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!

 

[Tom Wickerath]

It's just a matter of getting the criteria correct. Check out the form named
"frmTemperaturesWithBiAnnualStatistics" in an updated sample that I just
posted:

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
__________________________________________

 

[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?

 

[PPCO]

I'm very limited with SQL--can I get it to work with a query built into the
form?

 

[Tom Wickerath]

Yes, you should be able to add additional criteria. However, adding multiple
criteria to domain aggregrate functions can get a bit tricky at times, to get
it to work correctly. Are you really in need of these statistics on a form? A
report would be much easier.


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

 

[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

 

[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
__________________________________________