Results 1 to 4 of 4
  1. #1
    OBTech is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    2

    Which approach is best in my scenario

    Hi all,

    I've struggled with this for some time and need some guidance from you guys. It is something that is easily done in Excel, but I want to convert it to Access.
    In simple terms, my database is used to record, and report on, times set by unique cars in a competition comprising of two runs, with the faster time (FT) of the two runs, in seconds, used to decide the ranking order at the end.
    Example: 3 cars do two runs each, recording a time for each run. The best time of the two runs counts, and cars are ranked accordingly.

    The end report should look like this:

    Rank CarNo Run1 Run2 FT
    1 3 45.38 43.91 43.91
    2 1 46.82 45.17 45.17
    3 2 49.24 45.87 45.87

    The problem is I'm not sure whether to record times in a table with:
    (1) three cols - CarNo, RunNo and Time, e.g. (using car3 dataabove) 3 1 45.38 in one row and 3 2 43.91 in a second row, or
    (2) three cols - CarNo, Run1, and Run2, e.g. entering data to the first two cols as 3 45.38 in one row, and then updating that row with 43.91 in the run2 col.

    Even if this question is answered, how do I then establish the FT of each car before ranking?



    (A form would be used to record the times which would validate the car number against a pre-registered list of competitors in another table.)

    Over to you good people!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Use method 1 for a normalised table structure.

    Lots of methods of getting the fastest time
    1. Use DMin ... but this will be slow if you have many cars
    2. Create an aggregate query where you group by car and use Min value for time.
    Method 2 is much better.

    Then do a ranking query sorting by fastest time in ascending order

    It is possible to do this in one query by using a subquery but the above is probably easier.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    OBTech is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Aug 2018
    Posts
    2
    Thanks Colin.

    Method 1 is what I believed to be the right way to go about it.
    However, I'm stuck on how do I create the query/report that allows me to present the data in the way described in my OP, which I suspect should have been the focus of my question.
    Do I need to construct a new table with CarNo, Run1 and Run2 cols from the data recorded by Method 1?

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Create & populate your new table using method 1.
    Call it tblCarRace or similar with fields CarNo(PK), RunNo and RaceTime (not Time as its a reserved word)

    Create a query qryRaceTime similar to this:
    Code:
    SELECT tblCarRace.CarNo, Min(tblCarRace.RateTime) AS FastestTimeFROM tblCarRace
    GROUP BY tblCarRace.CarNo
    ORDER BY Min(tblCarRace.RateTime);
    To get the rank order, either use the query in a report or use a ranking query approach,
    I use the Serialize function for this: https://www.access-programmers.co.uk...ight=serialize
    Alternatively Allen Browne explains how to do this using subqueries here: http://allenbrowne.com/ranking.html

    Whichever method you prefer, adapt the above query to include it

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Please help with this scenario
    By irfanparbatani in forum Macros
    Replies: 5
    Last Post: 08-14-2014, 04:43 PM
  2. Comfusing Scenario
    By WAWA in forum Database Design
    Replies: 1
    Last Post: 04-30-2010, 10:49 AM
  3. Scenario help
    By RadBrad in forum Access
    Replies: 3
    Last Post: 08-02-2009, 10:35 PM
  4. Best approach to export to HTML in this scenario
    By techguy817 in forum Import/Export Data
    Replies: 0
    Last Post: 04-17-2009, 10:28 PM
  5. Can I UseTabs in This Scenario?
    By skyrise in forum Forms
    Replies: 0
    Last Post: 03-06-2009, 11:10 AM

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