Here is example on how to calculate recordset total using “select case” statement. I have calculated essay and resume scholarship values based on records from two essay and resume tables, and queried results based on applicant-student registration ID or name. Then I calculated “total score” of each student using totals from two categories they were judged upon: essay and resume.
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>
<head>
<title></title>
<link href=”Styles/Site960.css” rel=”stylesheet” type=”text/css” />
</head>
<body>
<%
Dim objConn, objRS ‘/conn str applicant tbl
Dim objConnE, objRSE ‘/conn str essay sum qry
Dim objConnR, objRSR ‘/conn str resume sum qry
Dim SumEss, SumEss1, i_select, i_select2, count, countRSumEss = 0
SumEss1 = 0
SumRes = 0
SumRes1 = 0
count = 0
countR = 0‘ / open db conn – connopen /
Set objConn = Server.CreateObject(“ADODB.Connection”)
dbPath = Server.MapPath(“scholarhip.accdb”)
objConn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & dbPath, “”, “”
SET objRS = Server.CreateObject(“ADODB.RecordSet”)
objRS.Open “SELECT aName, sProgram FROM Applicants WHERE sProgram = ‘Incoming Freshmen’ “,objConn,1,2Response.Write(“<table class=’datagrid’>”)
Response.Write(“<tr style=’background: #e1d6c7; font-weight: bold;’>”)
Response.Write(“<td>#</td><td>Applicant</td><td>Schol. Program</td><td>Essay</td><td>Resume</td><td>Final Score</td>”)
Response.Write(“</tr>”)
While not objRS.EOF
countR = countR + 1
Response.Write(“<tr>”)
Response.Write(“<td style=’text-align: right;’>”& countR &”.</td>”)
Response.Write(“<td>”& objRS(“aName”) &”</td>”)
Response.Write(“<td>”& objRS(“sProgram”) &”</td>”)
‘/ total essay
Response.Write(“<td style=’text-align: right;’>”)
SET objRSE = Server.CreateObject(“ADODB.RecordSet”)
objRSE.Open “SELECT * FROM Essay WHERE Applicant='”& objRS(“aName”)&”‘ “,objConn,1,2
Dim TotSum
While Not objRSE.EOF
count = count + 1
i_select = Right(count,1)
select case i_select
case “1”,”3″,”5″,”7″,”9″
SumEss = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
case “2”,”4″,”6″,”8″
SumEss1 = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
end select
objRSE.MoveNext
Wend
Response.Write(FormatNumber((SumEss) + (SumEss1),0))
SET objRSE = Nothing
‘objRSE.Close
Response.Write(“</td>”)‘/ total resume
Response.Write(“<td style=’text-align: right;’>”)
SET objRSR = Server.CreateObject(“ADODB.RecordSet”)
objRSR.Open “SELECT * FROM Resume WHERE Applicant='”& objRS(“aName”) &”‘ “,objConn,1,2
Dim TotSumR
While Not objRSR.EOF
count2 = count2 + 1
i_select2 = Right(count2,1)
select case i_select2
case “1”,”3″,”5″,”7″,”9″
SumRes = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
case “2”,”4″,”6″,”8″
SumRes1 = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
end select
objRSR.MoveNext
Wend
Response.Write(FormatNumber((SumRes) + (SumRes1),0))
SET objRSR = Nothing
Response.Write(“</td>”)
Response.Write(“<td style=’text-align: right;’>”)
Response.Write(FormatNumber((SumEss) + (SumEss1) + (SumRes) + (SumRes1),0))
Response.Write(“</td>”)
Response.Write(“</tr>”)
objRS.MoveNext
Wend
‘ close connections
SET objRS = nothing
SET objConn = nothingResponse.Write(“</table>”)
%>
</body>
</html>
