On Dec 20, 2:02 pm, Cory <C...@discussions.microsoft.com> wrote:
> How would I try this if these are not stored values? Each control is just
> calculated.
>
> "Minton M" wrote:
> > On Dec 20, 1:24 pm, Cory <C...@discussions.microsoft.com> wrote:
> > > I have the following calculated fields on a form:
>
> > > [Standard Time] and [Actual Time]
>
> > > There is a field that calculates the cumulative percentage using the formula
> > > TimeValue([Standard Time])/TimeValue([actual time]).
> > > I would like to have an average of the cumulative percentage on the form
> > > footer. Everything I try seems to error. I have tried to average and sum
> > > these fields to get the result but nothing is working for me. Any help will
> > > be appreciated.
>
> > I'm not sure you can perform aggregates on cumulative fields but I
> > could be wrong. The simple workaround is to create a query that
> > averages the TimeValue([Standard Time])/TimeValue([actual time])
> > calculation (since this will be the same value as the cumulative
> > average - unless my caffeine's not working today) and then use a
> > DLookup call to put this into your form.
>
> > -- James
Cory,
In a query you can have a calculated value that is the same formula as
your cumulative field (although it's not cumulative but won't matter
for this query).
For example:
select ID, [a]-[b] as MyValue
from WhateverTable
Getting the query to do the legwork will be faster than using controls
anyway. Then you just create a group by query that uses this one and
averages out the calculated value. Let me know if I need to be clearer
and I'll throw together an example.
-- James
|
Similar Threads
