I have a report containing a series of line graphs showing the number of service calls (for maintenance on equipment) performed each month. The service calls are categorized based on the reason for the service call. Im attempting to give a further breakdown of the progression of each category all on the same line graph. To do this i'm attempting to use a totals query with sub-queries(Using the IN() statement) to make this work. I can get the sub-query to return the data I need when run independently but it fails when combined into the main query. here's the further details...
The data needs to show a total number of service calls per month for the last 2 years, for each category & specific to each facility.
so I have set up the sub query with two tables Service Calls & Employees linked by an employee ID (employees table determines facility, in this case 'HTD'). I wrote an IN statement to look at the service calls ID (PCL_ID) and select only the ID's WHERE the JCDept field (facility) and PCL-Category match the criteria.
This runs just fine and gives me the data i need, so i then copied this SQL, removed the PCL_Date field, and pasted it as another IN() statement into my main query.
The main totals query will have a formatted column for each category to give me a total count by month for the past two years. Here is the resulting SQL for limited to just the first category.
The issue comes in when i run this, because it doesn't list the results by month, it instead counts the records and returns a yearly total which doesnt even match my calculated total(when done manually).
this is the first subquery im attempting so is there something im missing here?
The data needs to show a total number of service calls per month for the last 2 years, for each category & specific to each facility.
so I have set up the sub query with two tables Service Calls & Employees linked by an employee ID (employees table determines facility, in this case 'HTD'). I wrote an IN statement to look at the service calls ID (PCL_ID) and select only the ID's WHERE the JCDept field (facility) and PCL-Category match the criteria.
Code:
SELECT PressCallLog.PCL_Date, PressCallLog.PCL_ID
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
WHERE (((PressCallLog.PCL_ID) In (SELECT PCL_ID FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID WHERE (PCL_Category Like 'Material Variation') AND (JCDept = 'HTD'))));
This runs just fine and gives me the data i need, so i then copied this SQL, removed the PCL_Date field, and pasted it as another IN() statement into my main query.
The main totals query will have a formatted column for each category to give me a total count by month for the past two years. Here is the resulting SQL for limited to just the first category.
Code:
SELECT Format([PCL_Date],"yyyy-mm") AS [Year-Month], Count(PressCallLog.PCL_ID) AS [Material Variation]
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
GROUP BY Format([PCL_Date],"yyyy-mm")
HAVING (((Format([PCL_Date],"yyyy-mm"))>(Year(Now())-2)) AND ((Count(PressCallLog.PCL_ID)) In (SELECT PressCallLog.PCL_ID
FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID
WHERE (((PressCallLog.PCL_ID) In (SELECT PCL_ID FROM PressCallLog INNER JOIN dbo_ICEp_vwEmpBasic ON PressCallLog.PCL_Employee = dbo_ICEp_vwEmpBasic.EmpID WHERE (PCL_Category Like 'Material Variation') AND (JCDept = 'HTD')))))))
ORDER BY Format([PCL_Date],"yyyy-mm");
The issue comes in when i run this, because it doesn't list the results by month, it instead counts the records and returns a yearly total which doesnt even match my calculated total(when done manually).
this is the first subquery im attempting so is there something im missing here?