{"id":445,"date":"2011-01-17T17:38:14","date_gmt":"2011-01-17T21:38:14","guid":{"rendered":"http:\/\/www.iowawebnet.com\/ein\/?p=445"},"modified":"2011-01-17T18:00:29","modified_gmt":"2011-01-17T22:00:29","slug":"calculating-total-from-recordset-using-select-case","status":"publish","type":"post","link":"https:\/\/www.iowawebnet.com\/ein\/2011\/01\/calculating-total-from-recordset-using-select-case\/","title":{"rendered":"Calculating total from recordset using “select case”"},"content":{"rendered":"

<\/a>\"total<\/a>
\nHere is code in classic asp<\/strong><\/p>\n

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\u00a0 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.<\/p>\n

<!DOCTYPE html PUBLIC “-\/\/W3C\/\/DTD XHTML 1.0 Transitional\/\/EN” “http:\/\/www.w3.org\/TR\/xhtml1\/DTD\/xhtml1-transitional.dtd”><\/p>\n

<html xmlns=”http:\/\/www.w3.org\/1999\/xhtml”>
\n<head>
\n<title><\/title>
\n<link href=”Styles\/Site960.css” rel=”stylesheet” type=”text\/css” \/>
\n<\/head>
\n<body>
\n<%
\nDim objConn, objRS ‘\/conn str applicant tbl
\nDim objConnE, objRSE ‘\/conn str essay sum qry
\nDim objConnR, objRSR ‘\/conn str resume sum qry
\nDim SumEss, SumEss1, i_select, i_select2, count, countR<\/p>\n

SumEss = 0
\nSumEss1 = 0
\nSumRes = 0
\nSumRes1 = 0
\ncount = 0
\ncountR = 0<\/p>\n

‘ \/ open db conn – connopen \/
\nSet objConn = Server.CreateObject(“ADODB.Connection”)
\ndbPath = Server.MapPath(“scholarhip.accdb”)
\nobjConn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & dbPath, “”, “”
\nSET objRS = Server.CreateObject(“ADODB.RecordSet”)
\nobjRS.Open “SELECT aName, sProgram FROM Applicants WHERE sProgram = ‘Incoming Freshmen’ “,objConn,1,2<\/p>\n

Response.Write(“<table class=’datagrid’>”)
\nResponse.Write(“<tr style=’background: #e1d6c7; font-weight: bold;’>”)
\nResponse.Write(“<td>#<\/td><td>Applicant<\/td><td>Schol. Program<\/td><td>Essay<\/td><td>Resume<\/td><td>Final Score<\/td>”)
\nResponse.Write(“<\/tr>”)
\nWhile not objRS.EOF
\ncountR = countR + 1
\nResponse.Write(“<tr>”)
\nResponse.Write(“<td style=’text-align: right;’>”& countR &”.<\/td>”)
\nResponse.Write(“<td>”& objRS(“aName”) &”<\/td>”)
\nResponse.Write(“<td>”& objRS(“sProgram”) &”<\/td>”)
\n‘\/ total essay
\nResponse.Write(“<td style=’text-align: right;’>”)
\nSET objRSE = Server.CreateObject(“ADODB.RecordSet”)
\nobjRSE.Open “SELECT * FROM Essay WHERE Applicant='”& objRS(“aName”)&”‘ “,objConn,1,2
\nDim TotSum
\nWhile Not objRSE.EOF
\ncount = count + 1
\ni_select = Right(count,1)
\nselect case i_select
\ncase “1”,”3″,”5″,”7″,”9″
\nSumEss = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
\ncase “2”,”4″,”6″,”8″
\nSumEss1 = ((objRSE(“Originality”))+(objRSE(“Clarity”))+(objRSE(“Content”))+(objRSE(“Neatness”))+(objRSE(“Grammar”))+(objRSE(“Punctuation”)))
\nend select
\nobjRSE.MoveNext
\nWend
\nResponse.Write(FormatNumber((SumEss) + (SumEss1),0))
\nSET objRSE = Nothing
\n‘objRSE.Close
\nResponse.Write(“<\/td>”)<\/p>\n

‘\/ total resume
\nResponse.Write(“<td style=’text-align: right;’>”)
\nSET objRSR = Server.CreateObject(“ADODB.RecordSet”)
\nobjRSR.Open “SELECT * FROM Resume WHERE Applicant='”& objRS(“aName”) &”‘ “,objConn,1,2
\nDim TotSumR
\nWhile Not objRSR.EOF
\ncount2 = count2 + 1
\ni_select2 = Right(count2,1)
\nselect case i_select2
\ncase “1”,”3″,”5″,”7″,”9″
\nSumRes = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
\ncase “2”,”4″,”6″,”8″
\nSumRes1 = ((objRSR(“Presentation”))+(objRSR(“Adherence”))+(objRSR(“Activities”))+(objRSR(“Potential”)))
\nend select
\nobjRSR.MoveNext
\nWend
\nResponse.Write(FormatNumber((SumRes) + (SumRes1),0))
\nSET objRSR = Nothing
\nResponse.Write(“<\/td>”)
\nResponse.Write(“<td style=’text-align: right;’>”)
\nResponse.Write(FormatNumber((SumEss) + (SumEss1) + (SumRes) + (SumRes1),0))
\nResponse.Write(“<\/td>”)
\nResponse.Write(“<\/tr>”)
\nobjRS.MoveNext
\nWend
\n‘ close connections
\nSET objRS = nothing
\nSET objConn = nothing<\/p>\n

Response.Write(“<\/table>”)<\/p>\n

%>
\n<\/body>
\n<\/html><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Here is code in classic asp 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\u00a0 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 […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[33],"tags":[30,35,34,36],"class_list":["post-445","post","type-post","status-publish","format-standard","hentry","category-classic-asp","tag-access-2","tag-calculation","tag-classic-asp-2","tag-total"],"_links":{"self":[{"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/posts\/445"}],"collection":[{"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/users\/35"}],"replies":[{"embeddable":true,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/comments?post=445"}],"version-history":[{"count":23,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/posts\/445\/revisions"}],"predecessor-version":[{"id":478,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/posts\/445\/revisions\/478"}],"wp:attachment":[{"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/media?parent=445"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/categories?post=445"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.iowawebnet.com\/ein\/wp-json\/wp\/v2\/tags?post=445"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}