Keeping track of timing

[Design by Sue]

I am setting up a table to keep track of the time it takes to complete a training course. One of the fields needs to be the time it took to complete the course in minutes. What is suggested as the best field type for this? Number, text....? The field will be used in the future to produce reports that show this completion time and am require some calculation to average the times.

Thanks in advance!

 

[theDBguy]

I'd recommend a Long Integer field.

 

[Design by Sue]

Wow that was quick - thank you. I am thinking that I would, rather than an input mask, set the perimeters (sorry if that is the wrong word) for the field on the form - and limit it there to how I need it to appear.

 

[Gasman]

Why would you even consider text for that?

 

[tvanstiphout]

I'd recommend a Long Integer field.

An integer field would be good to store 32K minutes, which is about 533 hours or 22 days.

 

[Design by Sue]

Wasn't really considering text ... it is just one of the available options and I did see a post somewhere that thought this was a good idea. Again appreciate the input

 

[Gasman]

Wasn't really considering text ... it is just one of the available options and I did see a post somewhere that thought this was a good idea. Again appreciate the input

Well if I was entering the time, I would expect to do it as 2:45 ?
So that control would be text, and then convert that to minutes for storage or calculations.

I would not expect to have to calculate 2 * 60 and then add 45, to have to key in 165 ?

 

[GPGeorge]

I am setting up a table to keep track of the time it takes to complete a training course. One of the fields needs to be the time it took to complete the course in minutes. What is suggested as the best field type for this? Number, text....? The field will be used in the future to produce reports that show this completion time and am require some calculation to average the times.

Thanks in advance!

I'm going to suggest a different take, based in part on Gasman's comment.

Why not enter StartTime and EndTime and calculate the time it took to complete the task? Or, if there are multiple work sessions involved, the Start and End Times for each of those sessions, from which a total time can be calculated. If the user is responsible for tracking the time they spent on a course so they can add it up and enter it themselves, they're going to be doing it that way anyway, just as a manual, additional task.

 

[Design by Sue]

I'm going to suggest a different take, based in part on Gasman's comment.

Why not enter StartTime and EndTime and calculate the time it took to complete the task? Or, if there are multiple work sessions involved, the Start and End Times for each of those sessions, from which a total time can be calculated. If the user is responsible for tracking the time they spent on a course so they can add it up and enter it themselves, they're going to be doing it that way anyway, just as a manual, additional task.

The info is coming from an internal company form - I already discussed this method with the user and it is not practical for use in this situation. The time keeping is done at the class level and the teachers are not the one's entering the info. This method makes sense for the way the company does their processing.

 

[Design by Sue]

Well if I was entering the time, I would expect to do it as 2:45 ?
So that control would be text, and then convert that to minutes for storage or calculations.

I would not expect to have to calculate 2 * 60 and then add 45, to have to key in 165 ?

So if I understand you correctly have the table as a number but for the field on the form allow the user to enter hours:minutes and have code to convert it to minutes for entry in the table. That sounds good to me

 

[Gasman]

So if I understand you correctly have the table as a number but for the field on the form allow the user to enter hours:minutes and have code to convert it to minutes for entry in the table. That sounds good to me

Yes, and present it in that way when showing it.
Let's say the user carries out the courses over several days, taking them perhaps an hour or two at a time. You would have a record for each session, and then just sum them and format as hh:mm to show total time.

However you would need validation as to ensure it is a correct time format. Numeric before the : and numeric after.
In my last place of work, I used to round to 6 minutes, so could enter at 2.7 for 2:42 etc. Then the field could be summed easily as it was a number anyway.

 

[Design by Sue]

Great info here! Thanks all - I am sure based on the replies that I can do what I need

 

[Gasman]

Another method might be two controls, one for hours and the other for minutes, close together but separated by a :

 

[The_Doc_Man]

You CAN (if you wish) use a date field to record time data. You could then enter or report the time in hh:nn format. However, due to some unfortunate issues with what MS ASSUMES about date/time fields, you might have issues in formatting time-oriented things if they exceed one day. As for example if you added up the times for ALL classes combined for a given day or week. This is a formatting issue, not a numerical one, so it is possible to use date/time fields for your computational purposes.

IF you decide to go that way having been warned about the formatting issues, come back and ask how to reach a particular result with time fields.

As to using text fields, it is doable, but more work than using a numeric method such as integer minutes. You can't EASILY do math on text fields.

 

[Design by Sue]

Another method might be two controls, one for hours and the other for minutes, close together but separated by a :

That is what I am thinking of doing - thank you

 

[LarryE]

Here is a db with an example of using dates and times together on the form. It has 1 table, 1 query and 1 form but you can maybe use the form as a template. Take special note of how Hours are computed on the form. Dates and Times are kind of weird in ACCESS, but I think if you keep the data types and formats, it will work as intended. You will certainly need to calculate any total hours in a query.

 

[Design by Sue]

Here is a db with an example of using dates and times together on the form. It has 1 table, 1 query and 1 form but you can maybe use the form as a template. Take special note of how Hours are computed on the form. Dates and Times are kind of weird in ACCESS, but I think if you keep the data types and formats, it will work as intended. You will certainly need to calculate any total hours in a query.

Thank you - I will be working on this again Wednesday and will look at this closely! Really appreciate the help

 

[gemma-the-husky]

I would get the times entered as 24 hour values. If a course stage was 3.40 pm, get it entered as 15:40, and store it as a dare/time record. If the course extends more than a day, then you need to take the date into account. I am sure having the times stored in this way should make things easier, but you still need to be careful how you process them, taking into account the vagaries/peculiarities of date/time arithmetic.

If you have a course start time and end time, you can easily calculate the duration in minutes, using a long variable. So what do you need to calculate from all the timings you record?

Maybe an app that manages clock cards would help. You know, where an employee clocks in and out during the day, and you use the elapsed times to evaluate his pay.

 

[ebs17]

? DateDiff("n", #0:00#, #2:45#), DateAdd("n", 165, #0:00#)
 165          02:45:00

You can easily convert the minutes as a long into a DateTime value and vice versa. DateTime is also a number internally.
Text as a data type is more complicated, you want to calculate with numbers.

and am require some calculation to average the times

I would look at the following necessary calculations and then decide which data type I would use to store in the table.
The goal will always be to get results as directly and quickly as possible and to avoid having to convert and calculate back and forth multiple times.