CODEWITHSUNDEEP
sqlvbams-access
Jgz Tradamus
Jgz Tradamus

Reputation: 21

Query is too long

I have this query for counting data on my database's table and store it to a textbox, then save it to another table where it stores the count. I stored the data to my textboxes first because i need to put the count on their specific locations on my second database table. Is there any way to simplify this query? :

query = "SELECT (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance') as [Revenue Assurance]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department Like '%Compliance, Fraud Investigation Services%' OR (Department = 'Compliance, Fraud Investigation Services')) as [Compliance, Fraud Investigation Services]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department Like '%Compliance, Solutions and Services%' OR (Department = 'Compliance, Solutions and Services')) as [Compliance, Solutions and Services]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Head (Senior)')) as [txtRAHS]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Head')) as [txtRAH]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Sr. Manager')) as [txtRASRMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Manager')) as [txtRAMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Sr. Supervisor')) as [txtRASRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Supervisor')) as [txtRASUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Jr. Supervisor')) as [txtRAJRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Sr. Staff')) as [txtRASRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Staff')) as [txtRASTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Jr. Staff')) as [txtRAJRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Revenue Assurance' AND (P_Level = 'Project Staff')) as [txtRAPS]" & _
'[CFIS]
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Head')) as [txtCFISH]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Sr. Manager')) as [txtCFISSRMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Manager')) as [txtCFISMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Sr. Supervisor')) as [txtCFISSRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Supervisor')) as [txtCFISSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Jr. Supervisor')) as [txtCFISJRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Sr. Staff')) as [txtCFISSRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Staff')) as [txtCFISSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Jr. Staff')) as [txtCFISJRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Fraud Investigation Services' AND (P_Level = 'Project Staff')) as [txtCFISPS]" & _
'[CSS]
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Head')) as [txtCSSH]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Sr. Manager')) as [txtCSSSRMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Manager')) as [txtCSSMGR]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Sr. Supervisor')) as [txtCSSSRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Supervisor')) as [txtCSSSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Jr. Supervisor')) as [txtCSSJRSUP]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Sr. Staff')) as [txtCSSSRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Staff')) as [txtCSSSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Jr. Staff')) as [txtCSSJRSTF]," & _
" (SELECT COUNT (Employee_Name) FROM tblEmployees WHERE Department = 'Compliance, Solutions and Services' AND (P_Level = 'Project Staff')) as [txtCSSPS]" & _
" From tblEmployees"

Thanks

Upvotes: 1

Views: 140

Answers (2)

TFD
TFD

Reputation: 24534

Use the SQL IN clause

e.g.

SELECT
  P_Level,
  COUNT(Employee_Name) AS COUNT_Employee_Name,
FROM tblEmployees
WHERE Department = 'Revenue Assurance'
  AND P_Level IN ('Head (Senior)', 'Head', 'Sr. Manager', 'Manager', 'Sr. Supervisor', 'Supervisor' ... )
GROUP BY P_Level

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13233

select department, p_level, count(*) as num_employees
from tblemployees
group by department, p_level

To get the count of employees by department and p_level (all combinations)

If you need to have those aliases returned on each row, create another table in your database, three columns:

department, p_level, count_alias

Put each department and p_level combination onto one row, and your alias into the count_alias column.

Then, when you run the query I gave you, join tblemployees to the above 3 column table (using department and p_level) and return the count_alias.

Upvotes: 2

Related Questions