Calculating pace for a query

  • Thread starter Thread starter deckard
  • Start date Start date
D

deckard

I am writing a database for a road race. I need to convert
the finishing times into seconds, then divide by 3.1 (5K
distance). I am creating a field for the finishing time,
and am using build to create a new field for the output.
Any ideas on the script for such a field?
 
I am writing a database for a road race. I need to convert
the finishing times into seconds, then divide by 3.1 (5K
distance). I am creating a field for the finishing time,
and am using build to create a new field for the output.
Any ideas on the script for such a field?
Click to expand...

You should NOT store this calculated field in your Table. Instead,
calculate it as needed in a Query - just put

Pace: [FinishTime] / 3.1

in a vacant Field cell in the query.
 
I have done as you suggested previously, and I get an
decimal output that does not relate to the time in any
way. I should have been clearer, I never intended to
permanently store the data and was indeed generating a
field in the query. The calculation just doesn't seem to
work out.

I have actually solved a bit of the problem. I have it
worked down to seconds per mile.

First, I used build to write the following expression:

"total seconds: ((Hour([time]))*60+(Minute([time])))"

That parsed "Time" to total number of seconds (total
seconds). Next I divided the total number of seconds AND
converted to integer using the following expression:

"seconds per mile: Int([total seconds]/3.1)"

I now have to generate an expression that will convert a
raw integer back to time. Example: I need "310" to
read "5:10", or the time equivalent of 310 seconds. Once I
have that, I'll be home free.

Thanks for the response!

-----Original Message-----
I am writing a database for a road race. I need to convert
the finishing times into seconds, then divide by 3.1 (5K
distance). I am creating a field for the finishing time,
and am using build to create a new field for the output.
Any ideas on the script for such a field?
Click to expand...

You should NOT store this calculated field in your Table. Instead,
calculate it as needed in a Query - just put

Pace: [FinishTime] / 3.1

in a vacant Field cell in the query.


.
Click to expand...
 
I have actually solved a bit of the problem. I have it
worked down to seconds per mile.

First, I used build to write the following expression:

"total seconds: ((Hour([time]))*60+(Minute([time])))"

That parsed "Time" to total number of seconds (total
seconds). Next I divided the total number of seconds AND
converted to integer using the following expression:

"seconds per mile: Int([total seconds]/3.1)"

I now have to generate an expression that will convert a
raw integer back to time. Example: I need "310" to
read "5:10", or the time equivalent of 310 seconds. Once I
have that, I'll be home free.
Click to expand...

Note that a Date/Time value is a fraction OF A DAY - i.e. 0.25 is
6:00am. Date/Times aren't really suited for durations; they correspond
instead to an exact point in time. I take it (given your expression)
that you're storing the racetime in a Date/Time value. Another way to
get total seconds would be

86400*CDbl([time])

or

DateDiff("s", #00:00:00#, [Time])

To display 310 seconds as 5:10, I'd suggest just using an expression
like

[Total Seconds] \ 60 & Format([Total Seconds] MOD 60, ":00")

or if you really want it in a date/time,

DateAdd("s", [Total Seconds], #00:00:00#)
 
That's pretty close; the only problem is it is rounding
the seconds to the minute. I need to be able to get a time
that represents minute/second pace-per-mile.

Thanks for your help so far, you're definitely on the
right track.

-----Original Message-----
I have actually solved a bit of the problem. I have it
worked down to seconds per mile.

First, I used build to write the following expression:

"total seconds: ((Hour([time]))*60+(Minute([time])))"

That parsed "Time" to total number of seconds (total
seconds). Next I divided the total number of seconds AND
converted to integer using the following expression:

"seconds per mile: Int([total seconds]/3.1)"

I now have to generate an expression that will convert a
raw integer back to time. Example: I need "310" to
read "5:10", or the time equivalent of 310 seconds. Once I
have that, I'll be home free.
Click to expand...

Note that a Date/Time value is a fraction OF A DAY - i.e. 0.25 is
6:00am. Date/Times aren't really suited for durations; they correspond
instead to an exact point in time. I take it (given your expression)
that you're storing the racetime in a Date/Time value. Another way to
get total seconds would be

86400*CDbl([time])

or

DateDiff("s", #00:00:00#, [Time])

To display 310 seconds as 5:10, I'd suggest just using an expression
like

[Total Seconds] \ 60 & Format([Total Seconds] MOD 60, ":00")

or if you really want it in a date/time,

DateAdd("s", [Total Seconds], #00:00:00#)



.
Click to expand...
 
That's pretty close; the only problem is it is rounding
the seconds to the minute. I need to be able to get a time
that represents minute/second pace-per-mile.
Click to expand...

Umm... the code I posted is accurate to the second (which is as
accurate as Access will let you get). Could you post the actual code
you're using?
 
Ok, here goes...

First, I start with a field called "Time", Date/Time,
short time datatype.

In the query, I use the "Time" field in the following
manner:

Pace: Int(((Hour([time]))*60+(Minute([time])))/3.1)

This parses the minutes down to seconds and adds to the
seconds previously listed to arrive at the number of
seconds per unit (mile- but unimportant to the issue) in
integers (rounding out the decimals).

Now... this is where I need the conversion from integer
back to short time. The code you gave me read the minutes
accurately, but zeroed out the minutes. I'm wondering if
it is rounding to the nearest 60 personally, though I need
to test that to be certain (just occurred to me). The
sample you sent I modified to look thusly:

PPM:[pace]\60 & Format([pace] Mod 60,":""00""")

I'm obviously missing something. Can you see it?

Thanks,
Deckard
 
Problem Solved. Thanks for your help!

-----Original Message-----
I have actually solved a bit of the problem. I have it
worked down to seconds per mile.

First, I used build to write the following expression:

"total seconds: ((Hour([time]))*60+(Minute([time])))"

That parsed "Time" to total number of seconds (total
seconds). Next I divided the total number of seconds AND
converted to integer using the following expression:

"seconds per mile: Int([total seconds]/3.1)"

I now have to generate an expression that will convert a
raw integer back to time. Example: I need "310" to
read "5:10", or the time equivalent of 310 seconds. Once I
have that, I'll be home free.
Click to expand...

Note that a Date/Time value is a fraction OF A DAY - i.e. 0.25 is
6:00am. Date/Times aren't really suited for durations; they correspond
instead to an exact point in time. I take it (given your expression)
that you're storing the racetime in a Date/Time value. Another way to
get total seconds would be

86400*CDbl([time])

or

DateDiff("s", #00:00:00#, [Time])

To display 310 seconds as 5:10, I'd suggest just using an expression
like

[Total Seconds] \ 60 & Format([Total Seconds] MOD 60, ":00")

or if you really want it in a date/time,

DateAdd("s", [Total Seconds], #00:00:00#)



.
Click to expand...
 
Back
Top