Results 1 to 3 of 3
  1. #1
    awallace is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2021
    Posts
    1

    Question Run Racing Team Database

    First and foremost, I'm a fairly novice user.



    I want to create a database that tracks a cross country team. Data I want to manage includes athletes, their race times at each meet, PRs, mile splits, and historical data from prior years' meets.

    I've started a basic database with a few tables to capture the basic data: roster with ages and race distances they run, meet list, results from one meet.

    I would like to create a query that would predict race performance based on mile splits. And also display current PRs compared to prior year PRs.

    I've tried to create a query that would calculate 'Race Distance' * 'Mile Split 1' to calculate predicted race time, but I'm getting a formatting error that's not showing me minutes : seconds.

    I've done a lot of this in Excel already, but of course if I alter a sheet the formulas error out.

    Right now I don't have a ton of data to enter, but that will change as time goes on.

    I'd appreciate any help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Provide data and query for analysis. If you want to provide db, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    I would like to create a query that would predict race performance based on mile splits. And also display current PRs compared to prior year PRs.
    my big tip, particularly for novice users, is to break each process down to a bunch of tiny steps. then have one query do each tiny step, one at a time. this makes understanding what you are doing much simpler, and it also simplifies error capture, and makes it much simpler to make any future changes.

    to display current pr compared to last year's pr's
    steps required.
    1. identify the athlete, probably by id number
    2. identify all races in prior years by athlete for each race distance/course/event
    3. use an aggregate function to find the minimum time for each distance/course/event
    4. repeat steps 3. and 4. for the current year.
    5. identify the actual distance/course/event you want to make the comparison for
    6. match the lists from steps 3. and 4. to extract the relevant times.

    to predict race performance from mile splits is a bit more complex because you have not indicated whether the mile splits are in a race in progress (so that you are doing this on-course during the race) or a post-event analysis. you are probably aware that there are a number of different ways to calculate a predicted race performance from a time at a different distance. using, for example, a 5000 metre time to predict a marathon performance, or using an 800 metre time to predict 5000 metre performance, and so forth. there are also a number of online race prediction calculators, some of which will share with you their methodology and I would seriously advise you look at a number of them before deciding which to use.

    this is Pete Riegel's race time predictor as published in Runners World in 1997. this was principally designed for use in road racing scenarios so for cross country I would use its output as a kind of ballpark figure rather than a specific predictor but it will give you something to work with.

    where:
    D1 is the given distance
    T1 is the given time for D1
    D2 is the distance to predict a time for
    T2 is the calculated predicted time for D2

    D1 and D2 need to be measured in the same units. metres, miles, kilometres or whatever. and T1 and T2 are in seconds.

    the formula is: T2 = T1 x (D2/D1)1.06

    for example, using 5000 m in 16:00.4 to predict a time for the half marathon

    D1 = 5000
    T1 = 960.4
    D2 = 21097.5
    T2 = 960.4 x (21097.5/5000) x 1.06 = 4295.552268 = 1:11:35.5


    good luck with your project.


    Cottonshirt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Team Points tracking database
    By Daisy509th in forum Access
    Replies: 10
    Last Post: 01-19-2018, 08:16 AM
  2. Racing League Database Help
    By TangoWhiskey28 in forum Access
    Replies: 9
    Last Post: 12-20-2017, 05:25 PM
  3. team building database
    By ldypurple in forum Access
    Replies: 6
    Last Post: 03-06-2013, 04:34 PM
  4. Help with hockey team history database
    By Hockeyguy1924 in forum Database Design
    Replies: 20
    Last Post: 06-15-2012, 03:05 PM
  5. Sports Team database
    By Patriot7470 in forum Database Design
    Replies: 2
    Last Post: 03-17-2012, 01:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums