Running times for track & field events

What field type and format should I use to maintain times and distances from track events?

I've tried different formats and am not having much luck.

I need to be able to sort by times so text fields won't help me in that regard.

I would appreciate suggestions...thanks!

well that depends on how you are storing the data.....

For instance if a race began at 6:01:02 PM and the ending times were something like 6:04:20 then I would use the date/time format......

If you are recording the times by time ran..... 0:03:18 then I would still use the time format.

Now, if you are storing by seconds.......198 seconds, then I would store it as an integer

If you are storing by seconds and fractions of a second.... 198.005005 seconds then I would use number, either single or double format depending on the accuracy of the timer.

Distances will be stored as a number, probably single format (2 decimals) For instance, 1800.03 meters

Now, if you're using a time format not supported by access (0:03:18:018 - 3 minutes 3 seconds and 18/1000th of a second, then I would use a string and parse out the numbers-this is a little more complicated as you have to use instr to find the colons, convert to number and then make it number.....all-the-while having error checking in place converting strings to numbers. Depending on how you want to use the data, you may have to convert the number to time format...... for instance a decimal equal to time..... 3 minutes would equal .05 of an hour 18 seconds would equal .005 and 18/1000th of a second would equal .000005 of an hour giving you .055005 hours. If my math is correct (which may be wrong, as I'm operating on a 6 pack right now )

Basically, it comes down to the times that you are wanting to enter..... I'm guessing it will be seconds/fractions of seconds, in which it will be stored as a single or double type number depending on the accuracy. This makes the most sense since fractions of a second is still in decimal format..... when you want to convert to time, you divide by 60 for minutes, 3600 for hours.

Again.... how do you want to record the data?

Wow, thanks for the input. Yes, the times will be recorded in a MM:ss.99 (minutes, seconds, to the 100th second).

The text can then be converted to a number, correct so that I can sor tit accurately.

thanks!

As long as your events don't lap over into the hours I would be inclined (as ben suggested) to store it as a double value. So for instance a race that lasted 3 minutes 18.45 seconds I would store as 198.45. When you reported these values or sorted them it would be a lot easier for you to handle and if you wanted to make them pretty you could do conversions to show them in MM:SS.SS format as well.

I think I found a solution for what I want to do. I think I'm going to try to store the performances as text and use a format of MM:SS.ss for running events and use the same format for distance (throws and jumps) that will be interpreted as FF:II.ii (where FF are feet, II are inches and ii are quarter inches).

I've played with it a bit with sample data and it seems to work.

The only thing I may have a problem with is sorting from fastest/longest to slowest/shortest because of it being a text field....we'll just have to see.

Thanks for the suggestions!

If you store your data with leading zeros you should be able to sort them. For instance:



if you don't store the leading 0 something that is

10 minutes is going to appear before 8 minutes.