cumulative percentage

  • Thread starter Thread starter Cory
  • Start date Start date
C

Cory

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 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.
Click to expand...

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
 
How would I try this if these are not stored values? Each control is just
calculated.

Minton M said:
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.
Click to expand...

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
Click to expand...
 
How would I try this if these are not stored values? Each control is just
calculated.

Minton M said:
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.
Click to expand...
Click to expand...
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.
Click to expand...
-- James
Click to expand...
Click to expand...

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]- 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
 
If you could be a bit clearer and and example would help if you wouldn't
mind. Thanks.

Minton M said:
How would I try this if these are not stored values? Each control is just
calculated.

Minton M said:
I have the following calculated fields on a form:
Click to expand...
[Standard Time] and [Actual Time]
Click to expand...
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.
Click to expand...
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.
Click to expand...
-- James
Click to expand...
Click to expand...

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]- 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
Click to expand...
 
Back
Top