Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!
'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 |
---|---|---|---|
ERROR 3113 MODULEID NOT UPDATABLE | 8 | 22 | |
Gracefully handling 'Record Locked' Errors | 33 | 37 | |
How to create a multi-field primary key in Access using DAO in VBA? | 10 | 44 | |
append correct value from linked sharepoint list field column | 4 | 8 |
Join the community of 500,000 technology professionals and ask your questions.