1

I'm trying to get this query to select every department_id from my departments table, and then pass those values (there are about 30 or so) onto a sub query like so

SELECT Count(problems.id) AS Problems, 
departments.dname AS Company, 
departments.department_id AS CompanyID,
(SELECT COUNT(*) FROM problems WHERE problems.status BETWEEN 1 AND 8) AS totalopentickets, 
(SELECT COUNT(*) FROM problems WHERE status = 100) AS totalclosedtickets, 
(SELECT COUNT(problems.department) FROM problems WHERE problems.status = 100 AND problems.department = CompanyID) AS departmentclosed, 
(SELECT COUNT(problems.department) FROM problems WHERE problems.status BETWEEN 1 AND  8 AND problems.department = CompanyID) AS departmentopen
FROM problems 
INNER JOIN departments ON problems.department = departments.department_id
GROUP BY departments.dname, departments.department_id
ORDER BY Count(problems.id) DESC;

The idea is that it will grab all those values (1,2, 3 etc) then perform the sub query

(SELECT COUNT(problems.department) FROM problems WHERE problems.status BETWEEN 1 AND 8 AND problems.department = CompanyID)

With each of those values. This works in MySQL but I am unsure of how to get it to work in Access. Each time it asks me for a parameter then when I do I only get the values for the one parameter value.

Improve this question

1 Answer 1

Reset to default
1

I've managed to solve this just now by not using the alias but instead using the table and field name, like so

SELECT Count(problems.id) AS Problems, 
departments.dname AS Company, 
departments.department_id AS CompanyID,
(SELECT COUNT(*) FROM problems WHERE problems.status BETWEEN 1 AND 8) AS totalopentickets, 
(SELECT COUNT(*) FROM problems WHERE status = 100) AS totalclosedtickets, 
(SELECT COUNT(problems.department) FROM problems WHERE problems.status = 100 AND problems.department = departments.department_id) AS departmentclosed, 
(SELECT COUNT(problems.department) FROM problems WHERE problems.status BETWEEN 1 AND  8 AND problems.department = departments.department_id) AS departmentopen
FROM problems 
INNER JOIN departments ON problems.department = departments.department_id
GROUP BY departments.dname, departments.department_id
ORDER BY Count(problems.id) DESC;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.