How to list Race Results 1

[munrobagger]

Hi, I'm currently building a race results table. Is there a more elegant way to list the results, ie 1st, 2nd without creating a field for every placement. I'm thinking hard about autonumbers and lookup, to tie in with an existing runners table and race details table.

Much APPRECIATED!

 

[PHV]

Any chance you could elaborate on what you have and what you want to do ?
My guess is that you want a ranking query, don't you ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

have you read the fundamentals document linked below?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[OhioSteve]

PHV & LesPaul,

I have tremendous respect for your Access abilities. However, I think that you both misunderstood munrobagger's request. He knows how to order the rows. But he wants to print something like this:

seventh place...Jenny Smith
eigth place...George White
nineth place...Albert Jones

He wants to know how to elegantly convert the integer seven to the word "seventh".

munrobagger, correct me if I am wrong.

OhioSteve

 

[OhioSteve]

I found this nifty little function at

http://www.cpearson.com/Excel/ordinal.htm.

The author wrote it for Excel, but I tested it in Access for 1-20, and it worked. However, it generates strings like "11th" instead of "eleventh".



Function OrdinalNumber(ByVal Num As Long) As String

Dim N As Long
Const cSfx = "stndrdthththththth" ' 2 char suffixes

N = Num Mod 100
If ((Abs(N) >= 10) And (Abs(N) <= 19)) _
Or ((Abs(N) Mod 10) = 0) Then
OrdinalNumber= Format(Num) & "th"
Else
OrdinalNumber= Format(Num) & Mid(cSfx, _
((Abs(N) Mod 10) * 2) - 1, 2)
End If

End Function

 

[OhioSteve]

This knowledge base article covers something similar. You could adapt this code to your purpose in a few hours. However, if you have less than 100 participants it might be quicker just to make a lookup table with integers and strings.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213360

 

[munrobagger]

You were spot on Ohio. Will try this out. Hope this is not an innapropriate post.