Calculating Production Based on Time

[Teri]

Please help. If I'm in the wrong board, please let me
know. I need to calculate how long it takes someone to do
a particular task, then calculate how long it should've
taken them to perform the task based on benchmarks. An
example:
Process mail 25 pieces @ 25 mins total = 1 min

My first question is do I want to use the short time in
the table and base my calc on that, ie
Task count mins Mins/Trans
Process mail 25 0:25 0:01
When I use this, if there are no minutes in the table, I
still get mins/trans somehow, so I've tried :
SQL:
SELECT Mail.Team, Mail.Task, Mail.Processed,
Mail.HrsWorked, Format([hrsworked]/[processed],"hh:nn:ss")
AS MinsPerTrans
FROM Mail;

Can somehow please advise as to the best way to calculate
this type of data when there are hours:mins divided by a
whole number.????? I think I'm almost there but I'm
missing something. Thank you very much.

 

[Van T. Dinh]

Is your Field [HrsWorked] a DateTime Field?

If it is, you should note that DateTime Field is really
for an instance of date AND time and generally have some
drawbacks when you use DateTime Field for time duration.

In Access, DateTime values are stored similar to a Double
where the integral/whole number portion represent the
number of days since 31 Dec 1899 and the franctional
portion represent time since midnight as a fraction of a
day. The "date" component (which you don't see if you
use "short time" format) of the values of the Field can
give you the wrong calculated results.

The problem is not division by the whole number (but you
may need to be beware of division by zero).

It may be more appropriate to store the duration as the
whole number of the smallest unit of time (eg. minute or
second)rather than using a DateTime Field.

I am not sure from your post what you did prior to trying
the Query / SQL String. you wrote you did something that
gave you the minutes even when there is no minutes value
in the Table?

HTH
Van T. Dinh
MVP (Access)