How to find the second fastest time in a table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • CD Tom
    Contributor
    • Feb 2009
    • 495

    How to find the second fastest time in a table

    I am using Access 2007. I have a table that has a member number and up to eighteen different times. Table has Col. MbrNumber Time1, time2 etc.
    I want to be able to eliminate the fastest of the 18 times and save the second fastest time somewhere. What would be the an easy way to do this.
  • twinnyfo
    Recognized Expert Moderator Specialist
    • Nov 2011
    • 3653

    #2
    CD Tom,

    Your table makes this more difficult, because it has these values in separate fields, rather than in rows, but I can understand why you might want to have it set up this way.

    I would declare two public variables, one for the fastest time, another for the second fastest time.

    I would declare a function that could be called from within your code which would compare the current values of those two best times, and update the global variables as necessary. You have to determine where the first two time values fall first. You just need to determine what you will do with the final value.

    I won't give code for getting your table values into this loop, as you will probably either use a recordset, or draw directly from a form--your choice. Depends on how you are using this second best value.

    This should work, and rather quickly, since it is just cycling through 18 values.

    Let me know how it works.....

    Code:
    Option Compare Database
    Option Explicit
    
    Public dtmNumberOne As Date
    Public dtmNumberTwo As Date
    
    Private Sub FindTopTwo()
        dtmNumberOne = Time1
        If Time2 < dtmNumberOne Then
            dtmNumberTwo = dtmNumberOne
            dtmNumberOne = Time2
        Else
            If Time2 < dtmNumberTwo Then dtmNumberTwo = Time2
        End If
        EvaluateTime Time3
        EvaluateTime Time4
        EvaluateTime Time5
        'Etc.....
        Debug.Print dtmNumberTwo
    End Sub
    
    Private Function EvaluateTime(dtmTime As Date)
         If dtmTime < dtmNumberOne Then
            dtmNumberTwo = dtmNumberOne
            dtmNumberOne = dtmTime
        Else
            If dtmTime < dtmNumberTwo Then dtmNumberTwo = dtmTime
        End If
    End Function

    Comment

    • CD Tom
      Contributor
      • Feb 2009
      • 495

      #3
      thanks for the reply I think that will work.

      Comment

      • twinnyfo
        Recognized Expert Moderator Specialist
        • Nov 2011
        • 3653

        #4
        My pleasure! Let me know if there's anything else I can help with!

        Comment

        • zmbd
          Recognized Expert Moderator Expert
          • Mar 2012
          • 5501

          #5
          Database Normalization

          Originally posted by CD Tom
          I am using Access 2007. I have a table that has a member number and up to eighteen different times. Table has Col. MbrNumber Time1, time2 etc.
          I want to be able to eliminate the fastest of the 18 times and save the second fastest time somewhere. What would be the an easy way to do this.
          CD Tom:
          Taking a bit of time to normalize your database will really help out with this type of problem.
          I found the following to be a good review for myself and an excelent explanation of Database Normalzation concepts. Once your database is normalized, you find this information using a simple query without the need for VBA.

          As for the table format you have going, a cross-tab query would return the data in the format you have laid out.

          -z
          On Vacation with a Strange PC and a Funky Keyboard... where's that spell check... Rabbit... where'd that come from? Late for what... well it is 2am

          Comment

          Working...