Thread: Combining statements View Single Post
 12-16-2012, 03:33 PM PM User | #1 generaltomfool New to the CF scene   Join Date: Dec 2012 Posts: 3 Thanks: 2 Thanked 0 Times in 0 Posts Combining statements Hi guys, can you explain to me how I can combine these statements into one please? They all query the same table with different criteria so I guess it might be possible? Thank you Code: ```'find how many passes there are in this section set passRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 1 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS) strPass = passRS("passNo") passRS.CLOSE() SET passRS = NOTHING 'How many did student get? set passTotalRS=conn.execute("SELECT count(criteria_level) AS passNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 1 AND critieria_section_no = "&strSectionRS) strTotalPass = passTotalRS("passNo") passTotalRS.CLOSE SET passTotalRS = NOTHING 'find how many merits there are in this section set meritRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 2 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS) strMerit = meritRS("meritNo") meritRS.CLOSE SET meritRS = NOTHING 'How many did student get? set meritTotalRS=conn.execute("SELECT count(criteria_level) AS meritNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 2 AND critieria_section_no = "&strSectionRS) strTotalMerit = meritTotalRS("meritNo") meritTotalRS.CLOSE SET meritTotalRS = NOTHING 'find how many distinctions there are in this section set distinctRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_marks INNER JOIN z_"&strSchool&"_criteria ON marks_criteria_id = criteria_id WHERE criteria_level = 3 AND marks_username = '"&userRS("user_username")&"' AND critieria_section_no = "&strSectionRS) strDistinct = distinctRS("distinctNo") distinctRS.CLOSE SET distinctRS = NOTHING 'How many did student get? set distinctTotalRS=conn.execute("SELECT count(criteria_level) AS distinctNo FROM z_"&strSchool&"_criteria WHERE criteria_level = 3 AND critieria_section_no = "&strSectionRS) strTotalDistinct = distinctTotalRS("distinctNo") distinctTotalRS.CLOSE SET distinctTotalRS = NOTHING```