calculate time in report or query

[sue gray]

I am trying to calculate Earned and Used Time. I am really struggling with
this, do I put it in a query or the report. I really could use some help
with this. Thanks

Basically I need to be able to
If benefittype = s,
if amount > 0
add to sickamountearned,
else
add to sickamountused,
else if benefittype = v,
if amount >0
add to vacationamountearned,
else
add to vacationamountused,
else etc...

 

[NetworkTrade]

probably in the query; particulary if ultimately you are going to want to
sum, avg, etc those calculated time fields in your report.

you can search this site for Time Calculations and see alot of Q/A....

 

[sue gray]

Thanks for the response. I look over time calculations like you suggested.
Those don't really apply to me. I am dealing with just numbers positve and
negative. I guess I need some more help. Thanks.

 

[NetworkTrade]

if you are not working with time stamps, just regular numbers, it is indeed
easier. but you still probably want to do the calc in the query rather than
the report so that it is possible/easier to group sums/avgs at the end of
your report or the end of groups.

you want to build a calc field in your query using the Iif function i.e.

Tsickamount: Iif(FieldName1]>0, (this action),(Otherwise that action))

look up the Iif function on this site in Queries area or in Access Help,
these can be nested but that gets tricky to keep straight so start small and
simple to get the grasp....

when there is many levels of nesting sometimes it is easier to do some in
one calculated field and some in a second calculated field...and then create
a 3rd calculated field that works with those first 2....this may not make
sense at first but will once you get into it....

 

[John Spencer]

Where are you doing this? In a query?

Are you summing up times in an aggregate query?

If you are doing this in an aggregate query you would need a series of
calculated fields that look like

SickAmountEarned: Sum(IIF(BenefitType="S" and Amount>0,Amount,O))
SickAmountUsed: Sum(IIF(BenefitType="S" and Amount<0,Amount,O))

If you are doing this some place else, perhaps you would care to give us
a more detailed description of what you are attempting to accomplish.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[sue gray]

Thank you so much. That is exactly what I needed.