Sorting
Non smoker
Access 2000
I have a table with contenders of a running contest. Field names are name,
time1, time2, timeaverall
I sort the table on the timeoverall field.
Now I want to add 2 fields, containing each contenders position with time1
and time2
The position of the timeoverall I add with a text field in a report, so I
only want to add the Pos-fields for time1 and time2
Example:
Table:
Name time1 time2 timeoverall
aaa 01:15 12:13 13:28
bbb 01:14 13:01 14:15
ccc 02:18 10:10 12:28
...
The queryresult I want with the new Pos field
Name time1 Pos Time2 Pos Timeoverall
ccc 02:18 (3) 10:10 (1) 12:28
aaa 01:15 (2) 12:13 (2) 13:28
bbb 01:14 (1) 13:01 (3) 14:15
...
Who can/will help?
John W. Vinson
>Hi
>Access 2000
>
>I have a table with contenders of a running contest. Field names are name,
>time1, time2, timeaverall
>I sort the table on the timeoverall field.
Well... no, you don't.
A Table is an unordered "bag" of data. It has no defined sort order. If you
want a particular sort order you must - no options - use a Query sorting the
data.
>Now I want to add 2 fields, containing each contenders position with time1
>and time2
>The position of the timeoverall I add with a text field in a report, so I
>only want to add the Pos-fields for time1 and time2
>Example:
>Table:
>Name time1 time2 timeoverall
>aaa 01:15 12:13 13:28
>bbb 01:14 13:01 14:15
>ccc 02:18 10:10 12:28
>...
>The queryresult I want with the new Pos field
>
>Name time1 Pos Time2 Pos Timeoverall
>ccc 02:18 (3) 10:10 (1) 12:28
>aaa 01:15 (2) 12:13 (2) 13:28
>bbb 01:14 (1) 13:01 (3) 14:15
You do say that you want this in a query result, which is good: the pos field
should not exist in the table!
Try
SELECT [Name], [Time1], DCount("*", "yourtable", "[Time1] <= #" & [Time1] &
"*") AS Pos1, [Time2], DCount("*", "yourtable", "[Time2] <= #" & [Time1] &
"*") AS Pos2 FROM yourtable ORDER BY [Timeoverall];
A couple of comments:
- Name is a bad Name for the Name field. Everything in Access has a Name
property, and the program can get confused about whether Name refers to a
person's name, the name of a control, the name of a form, the name of a
field... I'd suggest using FirstName and LastName fields and concatenating
them for display or use a name like RunnerName.
- Timeoverall is redundant and should be calculated (by adding time1 and
time2) rather than stored as a field.
- date/Time fields are best used for exact points in date and time, not for
durations. If you're storing minutes and seconds, Access may get it confused
with hours and minutes (or vice versa); summing times over 24 hours won't
work; etc. You may want to store the race times in Long Integer seconds and
use some simple code to display them in minutes-seconds format.
John W. Vinson [MVP]
James A. Fortune
As John said, Date/Time data types aren't ideal for storing durations.
I also agree that TimeOverall is a separate calculation that should be
done in the query. I decided to try to solve this problem using some
time conversion functions.
tblRunnerTimes
RTID AutoNumber
RName Text
Time1 Date/Time Format: hh:nn (Note: This is really minutes and seconds
-- no 14 hour races!)
Time2 Date/Time Format: hh:nn
RTID RName Time1 Time2
1 ccc 2:18 10:10
2 aaa 1:15 12:13
3 bbb 1:14 1:01
4 ddd 1:14 1:02
'---Begin Module Code---
Public Function Seconds2DDHHNNSS(lngTotalSeconds As Long) As String
Dim intDays As Integer
Dim lngHours As Long
Dim intMinutes As Integer
Dim intSeconds As Integer
lngHours = lngTotalSeconds \ 3600
intDays = lngHours \ 24
lngHours = lngHours - intDays * 24
intMinutes = (lngTotalSeconds - intDays * 86400 - lngHours * 3600) \ 60
intSeconds = lngTotalSeconds - intDays * 86400 - lngHours * 3600 -
intMinutes * 60
Seconds2DDHHNNSS = Format(intDays, "00") & ":" & Format(lngHours, "00")
& ":" & Format(intMinutes, "00") & ":" & Format(intSeconds, "00")
End Function
Public Function HHNN2Seconds(dtTime As Date) As String
HHNN2Seconds = Hour(dtTime) * 60 + Minute(dtTime)
End Function
Public Function DDHHNNSS2Seconds(strTime As String) As Long
Dim lngDays As Integer
Dim intHours As Integer
Dim intMinutes As Integer
Dim intSeconds As Integer
Dim intColon As Integer
intColon = InStr(1, strTime, ":", vbTextCompare)
lngDays = CLng(Left(strTime, intColon - 1))
intHours = CInt(Mid(strTime, intColon + 1, 2))
intMinutes = CInt(Mid(strTime, intColon + 4, 2))
intSeconds = CInt(Right(strTime, 2))
DDHHNNSS2Seconds = intSeconds + 60 * CLng(intMinutes + 60 *
CLng(intHours + 24 * lngDays))
End Function
Public Function AddDDHHNNSS(strTime1 As String, strTime2 As String) As
String
AddDDHHNNSS = Seconds2DDHHNNSS(DDHHNNSS2Seconds(strTime1) +
DDHHNNSS2Seconds(strTime2))
End Function
Public Function SubtractDDHHNNSS2Seconds(strTime1 As String, strTime2 As
String) As Long
SubtractDDHHNNSS2Seconds = DDHHNNSS2Seconds(strTime1) -
DDHHNNSS2Seconds(strTime2)
End Function
'---End Module Code---
qryRunnerTimes:
SELECT RName, Seconds2DDHHNNSS(HHNN2Seconds(Time1)) AS FullTime1,
(SELECT Count(*) + 1 FROM tblRunnerTimes AS A WHERE DateDiff("s",
A.Time1, tblRunnerTimes.Time1) > 0) AS Rank1,
Seconds2DDHHNNSS(HHNN2Seconds(Time2)) AS FullTime2, (SELECT Count(*) + 1
FROM tblRunnerTimes AS A WHERE DateDiff("s", A.Time2,
tblRunnerTimes.Time2) > 0) AS Rank2, AddDDHHNNSS(FullTime1, FullTime2)
AS TimeOverall FROM tblRunnerTimes;
!qryRunnerTimes:
RName FullTime1 Rank1 FullTime2 Rank2 TimeOverall
ccc 00:00:02:18 4 00:00:10:10 3 00:00:12:28
aaa 00:00:01:15 3 00:00:12:13 4 00:00:13:28
bbb 00:00:01:14 1 00:00:01:01 1 00:00:02:15
ddd 00:00:01:14 1 00:00:01:02 2 00:00:02:16
Note: I tested the following expression from 1 to 10000000 (about 115
days of seconds):
lngI = DDHHNNSS2Seconds(Seconds2DDHHNNSS(lngI))
These functions overcome the following shortcomings:
1) You can use strings to store durations so that you aren't limited by
the Date/Time data type. I.e., you can use a Text field for Time1 and
Time2. Note: The SubtractDDHHNNSS2Seconds function can replace the
DateDiff function in the query when using strings for Time1 and Time2.
2) HHNN2Seconds can be widened so that you don't get a data type
mismatch when, say, you have 62 minutes. HHNN2Seconds can also be
adapted easily to take a string as input.
3) More than two durations can be added together.
If you know the two races won't be going over, say, an hour, you can
display just the right five characters of a FullTime string for Time1
and Time2.
James A. Fortune
MPAP...@FortuneJames.com
MPAP...@fortunejames.com
wrote:
> '---Begin Module Code---
> Public Function Seconds2DDHHNNSS(lngTotalSeconds As Long) As String
> Dim intDays As Integer
> Dim lngHours As Long
> Dim intMinutes As Integer
> Dim intSeconds As Integer
>
> lngHours = lngTotalSeconds \ 3600
> intDays = lngHours \ 24
> lngHours = lngHours - intDays * 24
> intMinutes = (lngTotalSeconds - intDays * 86400 - lngHours * 3600) \ 60
> intSeconds = lngTotalSeconds - intDays * 86400 - lngHours * 3600 -
> intMinutes * 60
> Seconds2DDHHNNSS = Format(intDays, "00") & ":" & Format(lngHours, "00")
> & ":" & Format(intMinutes, "00") & ":" & Format(intSeconds, "00")
> End Function
From:
http://groups.google.com/group/microsoft.public.access/msg/76d46a109b6613e7
and:
http://groups.google.com/group/comp.databases.ms-access/msg/3c63e62d4013b7ce
No Int() is necessary here because a Long is being passed in.
and:
http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/3209026f870fd8fd
No modification to Seconds = TimeElapsed Mod 60 is necessary for the
same reason.
I changed Seconds2DDHHNNSS as follows:
Public Function Seconds2DDHHNNSS(lngTotalSeconds As Long) As String
Dim intDays As Integer
Dim lngHours As Long
Dim intMinutes As Integer
Dim intSeconds As Integer
lngHours = lngTotalSeconds \ 3600
intDays = lngHours \ 24
lngHours = lngHours - intDays * 24
intMinutes = lngTotalSeconds \ 60 Mod 60
intSeconds = lngTotalSeconds Mod 60
Seconds2DDHHNNSS = Format(intDays, "00") & ":" & Format(lngHours,
"00") & ":" & Format(intMinutes, "00") & ":" & Format(intSeconds,
"00")
End Function
James A. Fortune
MPAP...@FortuneJames.com