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 =sum(a4:a9) 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("<table>" & vbcrlf)
  response.write("  <tr valign=""top"" align=""center"">" & vbcrlf)
  response.write("    <td><b>Golf Results</b></td>" & vbcrlf)
  response.write("  </tr>" & vbcrlf)
  response.write("  <tr>" & vbcrlf)
  response.write("    <td>" & vbcrlf)
  response.write("      <table>" & vbcrlf)
  response.write("        <tr>" & vbcrlf)
  response.write("          <td> </td>" & vbcrlf)
  response.write("          <td><b> Player Name</b></td>" & vbcrlf)
  response.write("          <td><b> Score </b></td>" & vbcrlf)
  response.write("        </tr>" & vbcrlf)
  if not rs is nothing then
    Do While Not rs.EOF
        response.write("      <tr valign=""top"">" & vbcrlf)
        response.write("        <td>" & rs.index & "</td>" & vbcrlf)
        response.write("        <td> " & rs.fields("playername").value & "</td>" & vbcrlf)
        response.write("        <td>" & rs.fields("score").value & "</td>" & vbcrlf)
        response.write("      </tr>" & vbcrlf)
        rs.movenext
    loop
      rs.close
  end if
  response.write("      </table>" & vbcrlf)
  response.write("    </td>" & vbcrlf)
  response.write("  </tr>" & vbcrlf)
  response.write("</table>" & 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]