[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-06
4
Medium Priority
?
197 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:JLohman
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Thomasian
ID: 36928554
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

0
 

Author Comment

by:JLohman
ID: 36928567
It is text field
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 2000 total points
ID: 36928573
Try
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

0
 

Author Comment

by:JLohman
ID: 36928582
I found the solution, I was missing ')' at the end of each line. Thank you for looking!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question