How to Count Based on Multiple Criteria From Multiple Tables

[hhzyousafi]

I have been at this a couple of hours and I just can't seem to make any headway. I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".

 

[plog]

Provide sample data, include the name of all relevant tables and fields. Also, post the exact resulting data that should be produced based on your starting sample data set. Use this format for posting:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 13, 2/16/2003
Sally, 48, 9/8/2007

 

[hhzyousafi]

tblTasks
TaskID,AssignedTo
1, Zohair Yousafi

tblTaskHistory
TaskID,TaskStatus
1, New
1, In Progress
1, Completed
1, Reopened
1, In Progress
1, Completed
1, Reopened

I want to be able to count how many times Zohair Yousafi's tasks were reopened. The count should be distinct as both tables have a one-to-many relationship. There could be multiple tasks that have been reopened multiple times in tblTaskHistory however I want the distinct count. In this particular case the result should be 1.

 

[plog]

You will need a subquery to find the distinct tasks that have been reopened:

SELECT tblTaskHistory.TaskID
FROM tblTaskHistory
WHERE (((tblTaskHistory.TaskStatus)="Reopened"))
GROUP BY tblTaskHistory.TaskID;

Paste the above code into a query and save it with the name 'TasksReopened_sub'. Then to get the actual results you want, use the below SQL:

SELECT tblTasks.AssignedTo, Count(TasksReopened_sub.TaskID) AS ReopenedTasks
FROM tblTasks INNER JOIN TasksReopened_sub ON tblTasks.TaskID = TasksReopened_sub.TaskID
GROUP BY tblTasks.AssignedTo;

 

[hhzyousafi]

Thank you very much Plog! That worked like a charm.