By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
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

✓ answered by twinnyfo

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.  

Share this Question
Share on Google+
4 Replies


twinnyfo
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
  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.  
Jul 26 '12 #2

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

twinnyfo
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

zmbd
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

Post your reply

Sign in to post your reply or Sign up for a free account.