asked on

Need a count of the number of times a number appears in multiple fields

I have an MSAccess database. For each record, I have 10 fields:
tblStaff.Grade1 tblStaff.Grade6
tblStaff.Grade2 tblStaff.Grade7
tblStaff.Grade3 tblStaff.Grade8
tblStaff.Grade4 tblStaff.Grade9
tblStaff.Grade5 tblStaff.Grade10

I need to count the number of cases where the number '7' appears in a field. Data in the fields will contain either a 7 or 8 or 9 or 10 or 11 or 12.

My SQL code is attached. I am getting the following error:

Microsoft JET Database Engine error '80040e14'

Missing ), ], or Item in query expression '( (select count(1) from tblStaff where Grade1 LIKE '%7%' + (select count(1) from tblStaff where Grade2 LIKE '%7%' + (select count(1) from tblStaff where Grade3 LIKE '%7%' + (select count(1) from tblStaff where Grade4 LIKE '%7%' + (select count(1) from tb'.

Dim sql, objRS, counter
 counter = 0

 sql = "SELECT tblStaff.Grade1, tblStaff.Grade2, tblStaff.Grade3, tblStaff.Grade4, tblStaff.Grade5, " & _
 		"tblStaff.Grade6, tblStaff.Grade7, tblStaff.Grade8, tblStaff.Grade9, tblStaff.Grade10, " & _
 		"( " & _
		"(select count(1) from tblStaff where Grade1 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade2 LIKE '%7%' +  " & _
		"(select count(1) from tblStaff where Grade3 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade4 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade5 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade6 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade7 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade8 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade9 LIKE '%7%' + " & _
		"(select count(1) from tblStaff where Grade10 LIKE '%7%') " & _ 
		") AS CountGS7 " & _

		"FROM tblStaff "

	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn

Open in new window

Thomasian

What is the data type of Grade1, Grade2,...? If it is numeric, use "= 7" instead of "LIKE '%7%'.

sql = "SELECT tblStaff.Grade1, tblStaff.Grade2, tblStaff.Grade3, tblStaff.Grade4, tblStaff.Grade5, " & _
	"tblStaff.Grade6, tblStaff.Grade7, tblStaff.Grade8, tblStaff.Grade9, tblStaff.Grade10, " & _
	"Abs(" & _
	"Grade1 LIKE '%7%' + Grade2 LIKE '%7%' + Grade3 LIKE '%7%' + Grade4 LIKE '%7%' + Grade5 LIKE '%7%' " & _
	"+ Grade6 LIKE '%7%' + Grade7 LIKE '%7%' + Grade8 LIKE '%7%' + Grade9 LIKE '%7%' + Grade10 LIKE '%7%' " & _
	"" & _
	") AS CountGS7 " & _
	"FROM tblStaff "

Open in new window

JLohman

ASKER

It is text field

ASKER CERTIFIED SOLUTION

Thomasian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JLohman

ASKER

I found the solution, I was missing ')' at the end of each line. Thank you for looking!