[Old Pedant]

Probably still want to do two queries, since there are two really very separate cases.

But your DB appears badly designed. Why would you *EVER* create separate tables for different schools? Why not one table for all schools with just a field to say *which* school? If there were some kind of security question involved, it might make sense, except *THEN* you should be using separate databases with separate logins/passwords for each database. It makes no sense at all to have separate tables in a single database from any perspective I can think of.

Anyway:

There are two basic ways: Get all 3 criteria in one record or get 3 records, one with each criteria level.

So... first, all 3 in one record:

marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
         & "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
         & "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
         & " FROM z_"&strSchool&"_marks, z_"&strSchool&"_criteria " _
         & " WHERE marks_criteria_id = criteria_id " _
         & " AND marks_username = '"&userRS("user_username")&"' " _
         & " AND critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
passNo = RS("passNo")
meritNo = RS("meritNo")
distnctNo = RS("distincNo")
RS.Close

marksSQL = "SELECT SUM(IF(criteria_level=1,1,0)) AS passNo, " _
         & "           SUM(IF(criteria_level,2,1,0) AS meritNo, " _
         & "          SUM(IF(criteria_level,2,1,0) AS distinctNo " _
         & " FROM z_"&strSchool&"_criteria " _
         & " WHERE critieria_section_no = "&strSectionRS
Set RS = conn.Execute( marksSql )
totalPassNo = RS("passNo")
totalMeritNo = RS("meritNo")
totalDistnctNo = RS("distincNo")
RS.Close

(I just could *NOT* bring myself to code that as strPassNo = RS("passNo") when *CLEARLY* the value of strPassNo would *NOT* be as string! It *WILL* be a number. A count (or sum) *MUST* be a number? Why would you ever named the variable str when it holds a number?

(Of course, I happen to be one of those who think that "Hungarian Notation" is an abomination. I have never opted to use prefixes on my variables such as "str" and "int" and "obj". Why MicroSLOP ever started that I will never know, but it's nice to know that even the Slop Shop has finally abandoned them, for the most part.)