Triathlon race times (1 Viewer)

[timbits35]

Hi,

I am creating an access database for the results of my triathlon times and I am having trouble with the race results. Based on some information I found here, I am using number fields for the swim, bike and run times because I want to do calculations and also sort them and it doesn't sort properly if they are text.

I have created separate fields for the hours, minutes and seconds for each of them.

Now I am trying to make a calculated field to convert the numbers to the following :

For a swim time, I want to convert it to the time per 100m. I have the calculation for that, but the result gives me the decimal portion and not the actual seconds portion and I am stuck. Here is what I have so far :

Sw100m: (([SwMin]+([SwSec]/60)+[SwHr]/60)*100/750)

So as you can see I have added up all the number to get the total number of minutes and then converted to the minutes per 100m (the race is 750m). And the result gives me 3.31. But the .31 is a decimal and I want to have seconds which is 18 seconds. I know how to do the calculation on paper but I don't know how to change my formula to fix this. On paper I have to multiply .31 x 60. But how do I refer to the decimal portion of the number and modify my formula?

Also if someone has a better idea of how to input race times as duration in an access database, please let me know.

Thank you

 

[vbaInet]

Not sure why you have separate fields but for 3.31 tell me what the Min, Sec and Hr values are.

 

[timbits35]

3.31 should be 3 minutes, 18 seconds

 

[vbaInet]

I'm talking about these fields, [SwMin] [SwSec] [SwHr], what are their values before the calculation?

 

[timbits35]

0 hours, 24 minutes, 53 seconds

 

[vbaInet]

Try this:

Sw100m: Format(((([SwMin]+([SwSec]/60)+([SwHr]*60))*(100/750))*60)/86400, "nn:ss")

 

[timbits35]

It works perfectly thanks. But can you explain what the 86400 represents?

 

[vbaInet]

Sure!

60 * 60 * 24 = 86400 in seconds.

 

[timbits35]

Hi again,

Now that I have the formula correct, I realize that the formula has fixed numbers. Meaning that I have hard-coded the swim, bike and run distances as 500m, 20km and 5km. And I use these numbers to calculate the km/hr. Unfortunately not all the triathlons have the same distances. The distances can vary.

For example a sprint triathlon usually is 750m swim, 20km bike and 5km run. But I have done some sprint triathlons where it is 500m or even 1km swim instead of 750m.

Does anyone have any suggestions as to how I can resolve this? And where I should specify the actual distances.

Here are my tables.

 

[vbaInet]

Why not show some records from your tblrace table.

 

[timbits35]

The tblrace does not show any of the distances. This comes from the tbldistance which looks like this :

Distance ID Distance
1 750m-20k-5k
2 1k-27k-8k
3 500m-20k-4k
4 5k
5 10k

The distance is a text field. I am wondering if I should add more fields that are numeric only for the swim, bike and run distances. Then I am not sure what to do. I have to be able to have Access know which distance it is to choose the correct numbers to use for my calculations. I am not a programmer so I am stumped on this one.

Also the form is based on the tblrace and it is not a query.

Thank you.

 

[vbaInet]

Ok, this table (and possibly others) is not well structured and you won't be able to accomplish your objective if it isn't addressed.

You'll need to create a new thread in the Tables section of the forum for help on normalising your table(s). Include all the tables in the post and someone will be able to advice.