Calculations within calculations

[Billy M]

Hi,

I am trying to develop a small database within which I have two fields:
called 'Drop off time' and 'pick up time', these are used to calculate a
feild called 'hours' ie customer dropped off at 0900hrs picked up at 1000hrs
= 1 hour. Each hour costs £4.00. I want to perform a calculation withina
query that gives the result of the first calculated field (1 hour) * the hour
rate.

No doubt easy but my knoledge is limted so any help appreciated.

Thanks.

 

[Allen Browne]

Type an expression like this into the Field row in query design:
[hour rate] * DateDiff("n", [drop off time], [pick up time]) / 60

Note that you should not store this in a table. Use a query instead.
More info about why in:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Clifford Bass]

Hi Billy,

I would suggest that you enter the date also into the drop off and pick
up time fields. The reason being that it makes it much easier to calculate
the hours if the thing is held overnight or longer. Note that there are no
just-date or just-time fields in Access, only date/time fields. These are
stored as real numbers with the integer part an offset in days (+ or -) from
30 Dec. 1899. The decimal part holds the time. So a date without a time is
really that date as of midnight. And a time without a date is really the
time on 30 Dec. 1899. Anyway do something like this:

select [drop off time], [pick up time], (DateDiff("n", [drop off time],
[pick up time]) - 1) \ 60 + 1 As Hours_Held, [Hours_Held] * [hour rate] As
Total_Charge
from ....

A few things: For the Hours_Held calculation I am presuming that a
fraction of an hour gets charged the whole hour. The \ does integer
division; that is it does the division and then drops the decimal part. If
you want to prorate the charges for partial hours use this instead:

Round(DateDiff("n", [drop off time], [pick up time]) / 60, 2)

And you may then want to round the second calculation so that you do
not get fractional pennies:

Round([Hours_Held] * [hour rate], 2)

Finally, Access allows you to reference one calculated field in a
succeeding calculation. This is not standard in SQL so if you migrate to
some other database you may need to repeat the entire first computation
within the second:

select [drop off time], [pick up time], (DateDiff("n", [drop off time],
[pick up time]) - 1) \ 60 + 1 As Hours_Held, ((DateDiff("n", [drop off time],
[pick up time]) - 1) \ 60 + 1) * [hour rate] As Total_Charge
from ....

Hope that helps,

Clifford Bass

 

[John W. Vinson]

Hi,

I am trying to develop a small database within which I have two fields:
called 'Drop off time' and 'pick up time', these are used to calculate a
feild called 'hours' ie customer dropped off at 0900hrs picked up at 1000hrs
= 1 hour. Each hour costs £4.00. I want to perform a calculation withina
query that gives the result of the first calculated field (1 hour) * the hour
rate.

No doubt easy but my knoledge is limted so any help appreciated.

Thanks.

Do it as two calculations. If you want to account for fractional hours (should
36 minutes cost £4.00 or £2.40??) you could use

Hours: DateDiff("n", [Drop off time], [Pick Up Time])/60

Cost: Round([Hour rate]*DateDiff("n", [Drop off time], [Pick up time])/60, 2)

You may need to change the Format of the controls in which these are
displayed.

 

[Billy M]

Many thanks to all who replied. Problem solved by first answer!
Thanks again