Count the number of results for multiple fields based on criteria.

For each record in my table (tblStaff) there are 10 fields (Grade1, Grade2, Grade3...). I want to count the number of times specific criteria appears in these fields.

This table is linked to another table (tblGeneral) with city and state information. The tables are linked together with ChartID.

I am attaching my code, sample database and a document showing how I want the results displayed. The yellow highlights are the counts I am trying to get.
Code.txt
sampleDB.mdb
Report.docx
Malloy1446Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert SchuttSoftware EngineerCommented:
Try this, rest of code left the same.
'------------------------------------------------------------------------------------
sql1410GS11 = "SELECT tblGeneral.City, tblGeneral.State, " & _
		"( " & _
			"(select count(1) from tblStaff where Grade1 LIKE '%11%' and Series1 LIKE '%1410%' and PositionStatus1 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade2 LIKE '%11%' and Series2 LIKE '%1410%' and PositionStatus2 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) +  " & _
			"(select count(1) from tblStaff where Grade3 LIKE '%11%' and Series3 LIKE '%1410%' and PositionStatus3 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade4 LIKE '%11%' and Series4 LIKE '%1410%' and PositionStatus4 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade5 LIKE '%11%' and Series5 LIKE '%1410%' and PositionStatus5 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade6 LIKE '%11%' and Series6 LIKE '%1410%' and PositionStatus6 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade7 LIKE '%11%' and Series7 LIKE '%1410%' and PositionStatus7 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade8 LIKE '%11%' and Series8 LIKE '%1410%' and PositionStatus8 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade9 LIKE '%11%' and Series9 LIKE '%1410%' and PositionStatus9 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID) + " & _
			"(select count(1) from tblStaff where Grade10 LIKE '%11%' and Series10 LIKE '%1410%' and PositionStatus10 LIKE '%Filled%' and tblStaff.ChartID= tblGeneral.ChartID)  " & _
		") AS CountFilled " & _
		"FROM tblStaff INNER JOIN tblGeneral ON tblStaff.ChartID= tblGeneral.ChartID " & _
		
		"WHERE tblStaff.Grade1 LIKE '%11%' and tblStaff.Series1 LIKE '%1410%' and tblStaff.PositionStatus1 LIKE '%Filled%' " & _
		"OR tblStaff.Grade2 LIKE '%11%' and tblStaff.Series2 LIKE '%1410%' and tblStaff.PositionStatus2 LIKE '%Filled%'  " & _
		"OR tblStaff.Grade3 LIKE '%11%' and tblStaff.Series3 LIKE '%1410%' and tblStaff.PositionStatus3 LIKE '%Filled%' " & _
		"OR tblStaff.Grade4 LIKE '%11%' and tblStaff.Series4 LIKE '%1410%' and tblStaff.PositionStatus4 LIKE '%Filled%' " & _
		"OR tblStaff.Grade5 LIKE '%11%' and tblStaff.Series5 LIKE '%1410%' and tblStaff.PositionStatus5 LIKE '%Filled%' " & _
		"OR tblStaff.Grade6 LIKE '%11%' and tblStaff.Series6 LIKE '%1410%' and tblStaff.PositionStatus6 LIKE '%Filled%' " & _
		"OR tblStaff.Grade7 LIKE '%11%' and tblStaff.Series7 LIKE '%1410%' and tblStaff.PositionStatus7 LIKE '%Filled%' " & _
		"OR tblStaff.Grade8 LIKE '%11%' and tblStaff.Series8 LIKE '%1410%' and tblStaff.PositionStatus8 LIKE '%Filled%' " & _
		"OR tblStaff.Grade9 LIKE '%11%' and tblStaff.Series9 LIKE '%1410%' and tblStaff.PositionStatus9 LIKE '%Filled%' " & _
		"OR tblStaff.Grade10 LIKE '%11%' and tblStaff.Series10 LIKE '%1410%' and tblStaff.PositionStatus10 LIKE '%Filled%' "
		
 		If Request.querystring("sort") = "" then
 			sql1410GS11 = sql1410GS11 & "ORDER BY City, State"
 		Else
  			sql1410GS11 = sql1410GS11 & "ORDER BY " & Request.querystring("sort")
  		End If			

	Set objRS1410GS11 = Server.CreateObject("ADODB.Recordset")
	objRS1410GS11.Open sql1410GS11, objConn


%>

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 12.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title><%=title%></title>

  
</head>

<body>

<h2><%=title%></h2>


<!------Series 1410 Grade 11------------------------------------------->
<p><b>Grade 11 -

<%
Response.write objRS("Count1410GS11") & "&nbsp;locations</b><br>"
	

If objRS("Count1410GS11") > 0 then
	dim columns, i
	columns = 5

	Response.Write "<TABLE class='GeneralTable' cellspacing='0' width='90%' border='1' cellpadding='5'><tr>"
	i = 1

	do until objRS1410GS11.EOF
		Response.write "<td valign='top'><font size='2'>" & objRS1410GS11("City") & ",&nbsp;" & objRS1410GS11("State")
		If CLng(objRS1410GS11("CountFilled")) > 1 Then Response.write " (" & objRS1410GS11("CountFilled") & ")"
   		Response.Write "</td>"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do it in only one query. Test it and if is the solution, insert it in your code.
SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, 
 SUM(IIF(tblStaff.Grade1='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade2='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade3='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade4='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade5='GS-11', 1, 0)),
 SUM(IIF(tblStaff.Grade6='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade7='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade8='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade9='GS-11', 1, 0)), SUM(IIF(tblStaff.Grade10='GS-11', 1, 0))
FROM tblGeneral INNER JOIN tblStaff ON tblGeneral.ChartID = tblStaff.ChartID
WHERE (Series1 = '1410'  OR Series2 ='1410' OR Series3 = '1410' OR Series4 ='1410' OR Series5 = '1410' OR Series6 = '1410' OR Series7 ='1410' OR Series8 ='1410' OR Series9 ='1410' OR Series10 = '1410')
 AND (PositionStatus1 = 'Filled'  OR PositionStatus2 ='Filled' OR PositionStatus3 = 'Filled' OR PositionStatus4 ='Filled' OR PositionStatus5 = 'Filled'
 OR PositionStatus6 = 'Filled' OR PositionStatus7 ='Filled' OR PositionStatus8 ='Filled' OR PositionStatus9 ='Filled' OR PositionStatus10 = 'Filled' )
GROUP BY tblGeneral.ChartID, tblGeneral.City, tblGeneral.State

Open in new window

0
Malloy1446Author Commented:
robert_schutt: Code is exactly what is needed.

Only question, what is cLng (line 72)?
0
Robert SchuttSoftware EngineerCommented:
That does a "convert to long", to make sure we're not comparing a string to a number (although that might work just as well).
0
Malloy1446Author Commented:
Thanks. Perfect solution.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.