Calculating time problems

[rzkl]

Im trying to calcaulate time for various activitys. I have a few tables listed below. One holds the activity and one holds how much time should be allotted to the specific activity. Generally these activities only take a few minutes or less to complete. I am having problems doing the various calculations on them. I keep getting error as the answer. My reasoning for where I came up with the time is 24 hours =1 1/1440 = .00069444 should equal the minutes in a day. In my queries I am able to add them all together and format the answer as Short Time and get the answer that I need, When I then try to add the fields that are formatted in short time to other queries is when the problem starts. I want to be able to sum everybody’s time for the day as well as do comparisons from the actual time VS the allotted standard. I think I have the way that I’m calculating the time correct by I could be completely wrong


Standards Table
Activity time per
1 .0006944
2 .0013888
3 .0010416

Activity Table

Date Employee activity qty
1/12/12 1 2 10
1/12/12 2 3 5
1/13/12 3 1 150

 

[jzwp22]

24 hours =1 1/1440 = .00069444 should equal the minutes in a day

Perhaps I am being dense today but there are 1440 minutes in a day, so the .00069444 is the fraction of a day represented by 1 minute.

Why do you not just store the actual number of minutes in your table for each activity?

You have to be careful when you format to short time because Access stores dates/times as a number not how it is typically displayed. For example 01/26/2012 12:28 PM is stored as 40934.5194444444. The portion of the number to the left of the decimal point represents the number of days since 12/31/1899 while the portion of the number to the right of the decimal point represents the fraction of a day. So the time fraction for 1 minute past midnight would be = .0006944444

When I then try to add the fields that are formatted in short time to other queries is when the problem starts. I want to be able to sum everybody’s time for the day as well as do comparisons from the actual time VS the allotted standard

I'm taking a guess here since I don't quite follow how you are storing information in your tables, but I would recommend doing all calculations in minutes. You can display the time in the short time format hh:mm using a separate field in your query to convert the time in minutes to the hh:mm format: INT(timeinminutes/60) & ":" & timeinminutes-60*int(timeinminutes/60)) but I would not use that field for calculation purposes (only display purposes). If you are worried about fractions of a minute, then I would store values as seconds and do all calculations on that field.

 

[rzkl]

I never thought of it that way but I believe that will work, I will have to go down to seconds as some of the activities are measured 200 per hour.

Thank you for the direction/suggestion

 

[jzwp22]

You're welcome. Please post back if you have other related questions.