Solved

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

Posted on 2011-03-17
31
339 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:JLohman
  • 15
  • 13
  • 2
  • +1
31 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 35160214
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
 
LVL 45

Expert Comment

by:Kdo
ID: 35160303

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
 

Author Comment

by:JLohman
ID: 35162581
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162654
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162661
kindly remove the duplicate 'FROM' my mistakes in copy & paste :)
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162717
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
 

Author Comment

by:JLohman
ID: 35162724
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162727
are you running this on Access or SQL Server?
0
 

Author Comment

by:JLohman
ID: 35162735
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35162770
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
 

Author Comment

by:JLohman
ID: 35162772
The data is located in Access.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35163920
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35163968
somehow Access use * character for LIKE operator not %
0
 

Author Comment

by:JLohman
ID: 35166156
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
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 50 total points
ID: 35168850
Those are table aliases.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35169540
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35169593
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
 

Author Comment

by:JLohman
ID: 35174177
In Access, I am getting an error:

            Syntax error (missing operator) in query expression '(
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35198112
did you get the code in  ID: 35169540 works in your program?
0
 

Author Comment

by:JLohman
ID: 35228896
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
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 450 total points
ID: 35228916
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
 

Author Comment

by:JLohman
ID: 35233717
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35238829
can you paste the code u using to display the data?
you should use "resVar" variable when displaying the data
0
 

Author Comment

by:JLohman
ID: 35242132
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35246068
- 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
 

Author Comment

by:JLohman
ID: 35246702
I removed all the commas. Results are all displaying as 0. It is NOT counting correctly.
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35247304
- 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
 

Author Comment

by:JLohman
ID: 35247502
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
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35247621
- what about changing the count(1) to count(*)?
0
 

Author Comment

by:JLohman
ID: 35248606
Also gives a result of 1 for each record.
0
 

Author Comment

by:JLohman
ID: 35355931
Still have not found a solution, does ANYONE have any ideas?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now