ASP Calculations

[artsapimp]

One of my projects is based on golf scores. I am writing the scores to an access database for each hole for each person (36 people). I want to have a leaderboard which updates automatically the lowest 5 scores.

I can write my SQL statement to ORDER by TotalScore which will put them in the correct order. But....

I can't figure out a way to make TotalScore equal hole1 + hole2 + hole3 + .... automatically. I know in excel you can write code like <b>=sum(a4:a9)</b> and it will do that. Is there something like this for access?

[artsapimp]

Come on UncaDanno.... I know you know this!

I have a temporary fix for it so it's not really important anymore. I do have another question though.

How do I make my SQL statement only choose the first 5 records? This has to be easy right?

[mosquito]

you could try:
select top 5 * from tablename

This works for SQLServer7. Don't know for Oracle or Access,
I assume that it works for access.

to do the sum thing, you could use a statement like this
select top 2 playername, sum(score)
from t_link l
inner join t_player p
on p.player_id = l.player_id
group by player

I assumed that you have 3 tables

t_player (player_id, playername,...) ==> player_id = PK
t_holes (hole_id,holedescription,...) ==> hole_id = PK
t_link (player_id,hole_id,score) ==> player_id + hole_id = PK


I hope this is what you wanted

[artsapimp]

Thank you.

That is probably exactly what I needed. I actually found another way of working it all out.

Your coding is a little over my head if it's .asp. I haven't seen any code like that before.

Thank you again for your response.

[UncaDanno]

Looks like I missed out on this one. But looks like you already have an answer, Art!

Kudos.

[artsapimp]

Thanks for replying though. Is that ASP code posted above? I thought I had learned a lot about ASP but I've never seen that style of coding. I will be out of the country until Monday so I will be unable to reply to anyone's comments.

Thanks again.

[mosquito]

I didn't write ASP code, I only supplied the SQL Statements you could use to retrieve the data you wanted.
I assume that conn is the ADO connection object you are using, and rs is the recordset object, and SQLQuery is a string variable to hold the SQL Statement


sub WriteResult()
dim conn
dim rs
dim SQLQuery

Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString = "Driver={SQL Server};Server=CARTMAN;UID=usr_intranet;PWD=intranet"
conn.CursorLocation = 3
conn.open

SQLQuery = "select top 5 playername, sum(score) score "
SQLQuery = SQLQuery & "from t_link l "
SQLQuery = SQLQuery & "inner join t_player p "
SQLQuery = SQLQuery & "on p.player_id = l.player_id "
SQLQuery = SQLQuery & "group by playername"

Set rs = conn.Execute(SQLQuery)
response.write("&lt;table&gt;" & vbcrlf)
response.write(" &lt;tr valign=""top"" align=""center""&gt;" & vbcrlf)
response.write(" &lt;td&gt;&lt;b&gt;Golf Results&lt;/b&gt;&lt;/td&gt;" & vbcrlf)
response.write(" &lt;/tr&gt;" & vbcrlf)
response.write(" &lt;tr&gt;" & vbcrlf)
response.write(" &lt;td&gt;" & vbcrlf)
response.write(" &lt;table&gt;" & vbcrlf)
response.write(" &lt;tr&gt;" & vbcrlf)
response.write(" &lt;td&gt;&nbsp;&lt;/td&gt;" & vbcrlf)
response.write(" &lt;td&gt;&lt;b&gt;&nbsp;Player Name&lt;/b&gt;&lt;/td&gt;" & vbcrlf)
response.write(" &lt;td&gt;&lt;b&gt;&nbsp;Score&nbsp;&lt;/b&gt;&lt;/td&gt;" & vbcrlf)
response.write(" &lt;/tr&gt;" & vbcrlf)
if not rs is nothing then
Do While Not rs.EOF
response.write(" &lt;tr valign=""top""&gt;" & vbcrlf)
response.write(" &lt;td&gt;" & rs.index & "&lt;/td&gt;" & vbcrlf)
response.write(" &lt;td&gt;&nbsp;" & rs.fields("playername").value & "&lt;/td&gt;" & vbcrlf)
response.write(" &lt;td&gt;" & rs.fields("score").value & "&lt;/td&gt;" & vbcrlf)
response.write(" &lt;/tr&gt;" & vbcrlf)
rs.movenext
loop
rs.close
end if
response.write(" &lt;/table&gt;" & vbcrlf)
response.write(" &lt;/td&gt;" & vbcrlf)
response.write(" &lt;/tr&gt;" & vbcrlf)
response.write("&lt;/table&gt;" & vbcrlf)

end sub

You can use this sub anywhere you want to create a table with the list of Golf Players.
I hope this makes things clear. If not, pop me a mail.

[Edited by mosquito on 09-14-2000 at 05:37 AM]