'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
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;
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
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
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
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
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
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
What is c.id=b.id?
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
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
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Auto Center Controls on a Form Based on Screen Size/Resolution in MS Access | 10 | 39 | |
FrontEnd tools to create web database application | 7 | 61 | |
DCount Type Mismatch | 2 | 22 | |
Html form and modal / img src -problem | 3 | 11 |
Join the community of 500,000 technology professionals and ask your questions.