SQL - Count number of times text occurs in multiple fields for one record

I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I have tblStaff with mutiple fields. I am working with the fields ChartID, Title1, Title2, TItle3, Title4, Title5, Title6, Title7, Title8, Title9, Title10. For each record in the database I want to count the number of times the word 'Techn' appears in the about 10 Title fields. I will then display that count for each ChartID. I have tried multiple SQL statements with no success. I am attaching code for the sql statements I have tried and the errors I am receiving.

Example of data for one record:
ID:  101
Title1: Technician
Title2: Clerk
Title3: Manager
Title4: Technical Support
Title5: Receptionist
Title6: Technician
Title7: Support
Title8: Secretary
Title9: Specialist
Title10: Technician

Results for CountTech should be
ID           # of tech positions
101                    4
'Following code generate the count for the number of records, is not counting where each field contains 'Techn'
'sqlCountTech = "SELECT COUNT(tblStaff.Title1 + tblStaff.Title2 + tblStaff.Title3 + tblStaff.Title4 + tblStaff.Title5 + tblStaff.Title6 + tblStaff.Title7 + tblStaff.Title8 + tblStaff.Title9 + tblStaff.Title10), count(*) AS CountTech " & _

'ERROR: You tried to execute a query that does not include the specified expression 
'sqlCountTech = "SELECT (tblStaff.Title1 + tblStaff.Title2 + tblStaff.Title3 + tblStaff.Title4 + tblStaff.Title5 + tblStaff.Title6 + tblStaff.Title7 + tblStaff.Title8 + tblStaff.Title9 + tblStaff.Title10), count(*) AS CountTech " & _

'ERROR: Syntax error
'sqlCountTech = "SELECT COUNT(*) FROM (Select DISTINCT Title1, Title2, Title3, Title4, Title5, Title6, Title7, Title8, Title9, Title10) AS CountTech " & _

'ERROR: Wrong number of arguments used with function in query expression 'COUNT
'sqlCountTech = "SELECT COUNT(tblStaff.Title1, tblStaff.Title2, tblStaff.Title3, tblStaff.Title4, tblStaff.Title5, tblStaff.Title6, tblStaff.Title7, tblStaff.Title8, tblStaff.Title9, tblStaff.Title10) AS CountTech " & _

'ERROR:  Missing ), ], or Item in query expression 'count(*) (tblStaff.Title1'
'sqlCountTech = "SELECT count(*) (tblStaff.Title1, tblStaff.Title2, tblStaff.Title3, tblStaff.Title4, tblStaff.Title5, tblStaff.Title6, tblStaff.Title7, tblStaff.Title8, tblStaff.Title9, tblStaff.Title10) AS CountTech " & _

'ERROR: Invalid use of '.', '!', or '()'. in query expression 'count(*) 
'sqlCountTech = "SELECT count(*) (tblStaff.Title1 + tblStaff.Title2 + tblStaff.Title3 + tblStaff.Title4 + tblStaff.Title5 + tblStaff.Title6 + tblStaff.Title7 + tblStaff.Title8 + tblStaff.Title9 + tblStaff.Title10) AS CountTech " & _

'ERROR: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect
'sqlCountTech = "SELECT count(*) as CountTech, " & _ 

'ERROR: The following is returning a result of 1 for every record
sqlCountTech = "SELECT COUNT(tblStaff.Title1 + tblStaff.Title2 + tblStaff.Title3 + tblStaff.Title4 + tblStaff.Title5 + tblStaff.Title6 + tblStaff.Title7 + tblStaff.Title8 + tblStaff.Title9 + tblStaff.Title10) AS CountTech " & _
		"FROM tblStaff " & _
		"WHERE tblStaff.Title1 LIKE '%" & Techn & "%' OR tblStaff.Title2 LIKE '%" & Techn & "%' " & _
		"OR tblStaff.Title3 LIKE '%" & Techn & "%' OR tblStaff.Title4 LIKE '%" & Techn & "%' " & _
		"OR tblStaff.Title5 LIKE '%" & Techn & "%' OR tblStaff.Title6 LIKE '%" & Techn & "%' " & _
		"OR tblStaff.Title7 LIKE '%" & Techn & "%' OR tblStaff.Title8 LIKE '%" & Techn & "%' " & _
		"OR tblStaff.Title9 LIKE '%" & Techn & "%' OR tblStaff.Title10 LIKE '%" & Techn & "%' "

	Set objCountTech = Server.CreateObject("ADODB.Recordset")
	objCountTech.Open sqlCountTech, objConn

Open in new window

JLohmanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
OP_ZaharinConnect With a Mentor Commented:
hi JLohman,
- sorry for the delay. try the following code. apparently i miss the "AS resVar" in line 11.
- so when you retrieving the result, you can use "resVar" variable that count the total:

SELECT b.ID, (
(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.id=b.id) +
(select count(1) from tblStaff d where d.Title2 LIKE '*Techn*' and d.id=b.id) +
(select count(1) from tblStaff e where e.Title3 LIKE '*Techn*' and e.id=b.id) +
(select count(1) from tblStaff f where f.Title3 LIKE '*Techn*' and f.id=b.id) +
(select count(1) from tblStaff g where g.Title3 LIKE '*Techn*' and g.id=b.id) +
(select count(1) from tblStaff h where h.Title3 LIKE '*Techn*' and h.id=b.id) +
(select count(1) from tblStaff i where i.Title3 LIKE '*Techn*' and i.id=b.id) +
(select count(1) from tblStaff j where j.Title3 LIKE '*Techn*' and j.id=b.id) +
(select count(1) from tblStaff k where k.Title3 LIKE '*Techn*' and k.id=b.id) 
) AS resVar
from tblStaff b group by b.id

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi JLohman,

There are several ways to do this, but all are a bit "wordy".

Here's one way.


Kent

SELECT id,
  case when locate (Title1, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title2, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title3, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title4, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title5, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title6, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title7, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title8, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title9, 'Techn') > 0 then 1 else 0 end +
  case when locate (Title10, 'Techn') > 0 then 1 else 0 end 
FROM tblstaff;

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:

Oh.  You say that you're using MS SQL Server.  You'll need to use the CHARINDEX function instead of LOCATE.  (This is tagged in the MySQL zone so I gave MySQL code.)

SELECT id,
  case when charindex ('Techn', Title1) > 0 then 1 else 0 end +
...
  case when charindex ('Techn', Title10) > 0 then 1 else 0 end
FROM tblstaff;


Kent
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
JLohmanAuthor Commented:
I attached the code based on your solution. I am getting the following error:

Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'case when CHARINDEX ('Techn', Title1) > 0 then 1 else 0 end + case when CHARINDEX ('Techn', Title2) > 0 then 1 else 0 end + case when CHARINDEX ('Techn', Title3) > 0 then 1 else 0 end + case when CHARINDEX ('Techn', Title4) > 0 then 1 else 0 end + case when '.

Just to clarify, I need to count any time the charstring 'Techn' appears, whether it is technician or technical, etc.
sqlCountTech = "SELECT ID, " & _
  "case when CHARINDEX ('Techn', Title1) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title2) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title3) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title4) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title5) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title6) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title7) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title8) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title9) > 0 then 1 else 0 end + " & _
  "case when CHARINDEX ('Techn', Title10) > 0 then 1 else 0 end  " & _
	"FROM tblstaff "

	Set objCountTech = Server.CreateObject("ADODB.Recordset")
	objCountTech.Open sqlCountTech, objConn

Open in new window

0
 
OP_ZaharinCommented:
try to run this in sql server editor. this will return number of matched condition for each fields:

SELECT (select count(1) from FROM tblStaff where tblStaff.Title1 LIKE '%Techn%') titleone,
(select count(1) from FROM tblStaff where  tblStaff.Title2 LIKE '%Techn%') titletwo,
(select count(1) from FROM tblStaff where  tblStaff.Title3 LIKE '%Techn%') titlethree,
(select count(1) from FROM tblStaff where  tblStaff.Title4 LIKE '%Techn%') titlefour,
(select count(1) from FROM tblStaff where  tblStaff.Title5 LIKE '%Techn%') titlefive,
(select count(1) from FROM tblStaff where  tblStaff.Title6 LIKE '%Techn%') titlesix,
(select count(1) from FROM tblStaff where  tblStaff.Title7 LIKE '%Techn%') titleseven,
(select count(1) from FROM tblStaff where  tblStaff.Title8 LIKE '%Techn%') titleeight,
(select count(1) from FROM tblStaff where  tblStaff.Title9 LIKE '%Techn%') titlenine,
(select count(1) from FROM tblStaff where  tblStaff.Title10 LIKE '%Techn%') titleten

Open in new window

0
 
OP_ZaharinCommented:
kindly remove the duplicate 'FROM' my mistakes in copy & paste :)
0
 
OP_ZaharinCommented:
OR if you require the total count for all the ten fields:

SELECT (select count(1) from tblStaff where tblStaff.Title1 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title2 LIKE '%Techn%') +
(select count(1) from  tblStaff where  tblStaff.Title3 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title4 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title5 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title6 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title7 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title8 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title9 LIKE '%Techn%') +
(select count(1) from tblStaff where  tblStaff.Title10 LIKE '%Techn%') TotalTechn

Open in new window

0
 
JLohmanAuthor Commented:
OP Zaharin: your code is generating the following error:

Syntax error (missing operator) in query expression '(select count(1) FROM tblStaff where tblStaff.Title1 LIKE '%Techn%') Title1'.

I am attaching code. I do need to use the COUNT with the ID. I also do not see where you are assigning the variable.
sqlCountTech = "SELECT (select count(1) FROM tblStaff where tblStaff.Title1 LIKE '%Techn%') Title1, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title2 LIKE '%Techn%') Title2, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title3 LIKE '%Techn%') Title3, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title4 LIKE '%Techn%') Title4, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title5 LIKE '%Techn%') Title5, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title6 LIKE '%Techn%') Title6, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title7 LIKE '%Techn%') Title7, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title8 LIKE '%Techn%') Title8, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title9 LIKE '%Techn%') Title9, " & _
	"(select count(1) from FROM tblStaff where tblStaff.Title10 LIKE '%Techn%') Title10 "

	Set objCountTech = Server.CreateObject("ADODB.Recordset")
	objCountTech.Open sqlCountTech, objConn

Open in new window

0
 
OP_ZaharinCommented:
are you running this on Access or SQL Server?
0
 
JLohmanAuthor Commented:
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003
0
 
OP_ZaharinCommented:
where is tblStaff table located? in SQL Server or Access? Because the sql I've prepared works in SQL Server. I did not test it on Access.
0
 
JLohmanAuthor Commented:
The data is located in Access.
0
 
OP_ZaharinCommented:
hi jlohman,
i've simulate your table structuer and run this sql on Access database and hopefully this is the desired results you are loooking for:

SELECT b.id, (
(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.id=b.id) +
(select count(1) from tblStaff d where d.Title2 LIKE '*Techn*' and d.id=b.id) +
(select count(1) from tblStaff e where e.Title3 LIKE '*Techn*' and e.id=b.id) +
(select count(1) from tblStaff f where f.Title3 LIKE '*Techn*' and f.id=b.id) +
(select count(1) from tblStaff g where g.Title3 LIKE '*Techn*' and g.id=b.id) +
(select count(1) from tblStaff h where h.Title3 LIKE '*Techn*' and h.id=b.id) +
(select count(1) from tblStaff i where i.Title3 LIKE '*Techn*' and i.id=b.id) +
(select count(1) from tblStaff j where j.Title3 LIKE '*Techn*' and j.id=b.id) +
(select count(1) from tblStaff k where k.Title3 LIKE '*Techn*' and k.id=b.id) 
)
from tblStaff b group by b.id

Open in new window

0
 
OP_ZaharinCommented:
somehow Access use * character for LIKE operator not %
0
 
JLohmanAuthor Commented:
I don't understand the meaning of your b., c., d., ....
What is c.id=b.id?
What is the variable name for the record results?
0
 
SharathConnect With a Mentor Data EngineerCommented:
Those are table aliases.
0
 
OP_ZaharinCommented:
yes. the b,c, d... is a table alias. we use the table alias because we are doing query on the same table multiple times to get the result we desired.

in below codes i added resVar as a variable for the total count result. you can use ID and resVar variable to display the results.

try to copy-paste and run this in Access (under menu Create-Query Design-choose tblStaff table and click Close-View-SQL View and paste this code-and click Run).

it will be much clearer to you.

SELECT b.ID, (
(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.id=b.id) +
(select count(1) from tblStaff d where d.Title2 LIKE '*Techn*' and d.id=b.id) +
(select count(1) from tblStaff e where e.Title3 LIKE '*Techn*' and e.id=b.id) +
(select count(1) from tblStaff f where f.Title3 LIKE '*Techn*' and f.id=b.id) +
(select count(1) from tblStaff g where g.Title3 LIKE '*Techn*' and g.id=b.id) +
(select count(1) from tblStaff h where h.Title3 LIKE '*Techn*' and h.id=b.id) +
(select count(1) from tblStaff i where i.Title3 LIKE '*Techn*' and i.id=b.id) +
(select count(1) from tblStaff j where j.Title3 LIKE '*Techn*' and j.id=b.id) +
(select count(1) from tblStaff k where k.Title3 LIKE '*Techn*' and k.id=b.id) 
) resVar
from tblStaff b group by b.id

Open in new window

0
 
OP_ZaharinCommented:
What is c.id=b.id?

i pickup part of the code to explain this to you in a simple manner.

SELECT b.ID, (
(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.id=b.id)
from tblStaff b group by b.id

I would use "main Select table" and "secondary Select table" term to explain this:
1- we are doing 'select in select' in this SQL, therefore we need to tell the database to differentiate between the "main Select table" and the "secondary Select table" (the main and the secondary can be a same table or different table - in our case we are using the same table).

2- then we need to get the count result for the Title1+Title2+ (and so on) for the same ID. this is where we need to use the c.id=b.id.
-we are telling the database for each ID that you select from the "main Select table", go and search again in the "secondary Select table" (same table) for the Title that match 'Techn' AND the id for "secondary Select table" must be the same as the id in "main Select table".

hopefully my explanation helps you understand.
0
 
JLohmanAuthor Commented:
In Access, I am getting an error:

            Syntax error (missing operator) in query expression '(
0
 
OP_ZaharinCommented:
did you get the code in  ID: 35169540 works in your program?
0
 
JLohmanAuthor Commented:
I pasted the code from ID: 35169540 into MS Access. That is where I am getting the following error:

Syntax error (missing operator) in query expression '(
0
 
JLohmanAuthor Commented:
The code is working and counting correctly in MSAccess.

When I paste the SQL code in FrontPage, the page displays correctly, but results all display as the number 1.

0
 
OP_ZaharinCommented:
can you paste the code u using to display the data?
you should use "resVar" variable when displaying the data
0
 
JLohmanAuthor Commented:
My display code is:
       Response.Write "<TD>" & objCountTech("resVar") & "</TD>" & VbCrLf

For clarification:
In MSAccess: to search for the string TECH I use      LIKE '*Techn*'
       (this returns the correct counts)
In FrontPage
    - If I use the code  LIKE '*Techn*'   my results are all '0'
      Plus I get an error on the Set objCountTech line
    - If i use the code LIKE '%" & Techn & "%'   my results are all '1'
      No other errors
      This is the code that works in other counts to search for a particular string of characters
sqlCountTech = "SELECT b.ChartID, ( " & _
	"(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff d where d.Title2 LIKE '*Techn*' and d.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff e where e.Title3 LIKE '*Techn*' and e.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff f where f.Title4 LIKE '*Techn*' and f.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff g where g.Title5 LIKE '*Techn*' and g.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff h where h.Title6 LIKE '*Techn*' and h.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff i where i.Title7 LIKE '*Techn*' and i.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff j where j.Title8 LIKE '*Techn*' and j.ChartID=b.ChartID) +, " & _
	"(select count(1) from tblStaff k where k.Title9 LIKE '*Techn*' and k.ChartID=b.ChartID) " & _ 
	") AS resVar " & _
	"FROM tblStaff AS b GROUP BY b.ChartID; "

	Set objCountTech = Server.CreateObject("ADODB.Recordset")
	objCountTech.Open sqlCountTech, objConn

Open in new window

0
 
OP_ZaharinCommented:
- kindly try the following code. i take out the (,) sign after each (+). it should not be there:


sqlCountTech = "SELECT b.ChartID, ( " & _
	"(select count(1) from tblStaff c where c.Title1 LIKE '*Techn*' and c.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff d where d.Title2 LIKE '*Techn*' and d.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff e where e.Title3 LIKE '*Techn*' and e.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff f where f.Title4 LIKE '*Techn*' and f.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff g where g.Title5 LIKE '*Techn*' and g.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff h where h.Title6 LIKE '*Techn*' and h.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff i where i.Title7 LIKE '*Techn*' and i.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff j where j.Title8 LIKE '*Techn*' and j.ChartID=b.ChartID) + " & _
	"(select count(1) from tblStaff k where k.Title9 LIKE '*Techn*' and k.ChartID=b.ChartID) " & _ 
	") AS resVar " & _
	"FROM tblStaff AS b GROUP BY b.ChartID; "

	Set objCountTech = Server.CreateObject("ADODB.Recordset")
	objCountTech.Open sqlCountTech, objConn

Open in new window

0
 
JLohmanAuthor Commented:
I removed all the commas. Results are all displaying as 0. It is NOT counting correctly.
0
 
OP_ZaharinCommented:
- it works well in your Access, not sure why it doesnt work in ASP.
- other method i would suggest is to change the change the * to % eg: '*Techn*' to '%Techn%' which I guess the right SQL syntax since you are using ADO.


0
 
JLohmanAuthor Commented:
I did try that also. when I use '%Techn%' I get a result of 1 for each record. It still does NOT count correctly.
0
 
OP_ZaharinCommented:
- what about changing the count(1) to count(*)?
0
 
JLohmanAuthor Commented:
Also gives a result of 1 for each record.
0
 
JLohmanAuthor Commented:
Still have not found a solution, does ANYONE have any ideas?
0
All Courses

From novice to tech pro — start learning today.