426,234 Members | 1,832 Online
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

# How to find the second fastest time in a table

 100+ P: 443 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. Jul 26 '12 #1

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.....

Expand|Select|Wrap|Line Numbers
1. Option Compare Database
2. Option Explicit
3.
4. Public dtmNumberOne As Date
5. Public dtmNumberTwo As Date
6.
7. Private Sub FindTopTwo()
8.     dtmNumberOne = Time1
9.     If Time2 < dtmNumberOne Then
10.         dtmNumberTwo = dtmNumberOne
11.         dtmNumberOne = Time2
12.     Else
13.         If Time2 < dtmNumberTwo Then dtmNumberTwo = Time2
14.     End If
15.     EvaluateTime Time3
16.     EvaluateTime Time4
17.     EvaluateTime Time5
18.     'Etc.....
19.     Debug.Print dtmNumberTwo
20. End Sub
21.
22. Private Function EvaluateTime(dtmTime As Date)
23.      If dtmTime < dtmNumberOne Then
24.         dtmNumberTwo = dtmNumberOne
25.         dtmNumberOne = dtmTime
26.     Else
27.         If dtmTime < dtmNumberTwo Then dtmNumberTwo = dtmTime
28.     End If
29. End Function
30.

4 Replies

 Expert Mod 2.5K+ P: 3,212 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..... Expand|Select|Wrap|Line Numbers 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   Jul 26 '12 #2

 100+ P: 443 thanks for the reply I think that will work. Jul 27 '12 #3

 Expert Mod 2.5K+ P: 3,212 My pleasure! Let me know if there's anything else I can help with! Jul 30 '12 #4

 Expert Mod 5K+ P: 5,397 @CD Tom 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 Jul 31 '12 #5