How to do 2 counts in one SQL question?

[Speedy]

Hi,

first of all I hope I post this in the right thread, second of all I hope my title is somewhat close to what I'm asking for ;)

What I have is an asp page connected to an Access DB that contains
2 tables:

tbl_player and tbl_res

tbl_player [Id, Namn (First name), Efternamn (Last name), PlayerId (3 first letters in Namn and 3 first letters in Efternamn like this "Speedy Gonsalez" would be SpeGon)

tbl_res [Id, PlayerId, AMT1 (hole 1 on a golfcourse), AMT2 (hole 2 on a golfcourse), and so on up to AMT18, TOTAL (The score of a golfround)

What I wanna do here is to get the average for each player on each hole and list the ten lowest averages on the hole clicked ( I have a link for each hole)
but as you probably noticed I don't have the average saved in my DB, that's because we input hole by hole on each player at each competition to get the stats at the end of the season.

I have no trouble to get the average on each hole for a single player.

You can check out a test page here: http://www.sundbybergsbgk.se/stat05/stat.asp

(Just click a name to see the stats for that player)

as this is a test page there is no links for each hole on it, this only shows how i get the average listed, my intention is to link all holes (Bana 1, Bana 2 and so on)

Any ideas? If you miss some information just ask and I will try to answer it =)

/Speedy

[Speedy]

Ok, I got it to work by using this code:


<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("db/db.mdb")

Dim strLane
strLane = Request.Querystring("Lane")

Set objrs = Conn.Execute("SELECT TOP 10 tbl_res.PlayerId, Round(Avg(AMT"&strLane&"+0),3) AS medel, tbl_player.Namn, tbl_player.Efternamn FROM tbl_res INNER JOIN tbl_player ON tbl_res.PlayerId = tbl_player.PlayerId GROUP BY tbl_res.PlayerId, tbl_player.Namn, tbl_player.Efternamn ORDER BY Avg(AMT"&strLane&"+0)")
%>


but now I have a new question, Can I somehow type out what position a player has for a specific lane?

Example:

with the script above let's say it prints out the following list

John Doe 1,23
Jane Doe 2,34
Coding Forums 3,45
and so on up to 10 players.

can I somehow get the script to understand that Jane Doe is in second place and type that out in another page?

/S

[neocool00]

Are you wanting to replace "2" with "second"? If so, you can wrap a case statement around the column that is being returned. This should be somewhat easy as you are only returning the top 10 records.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END