I'm creating a query to breakdown timesheet hours by activity, this much I can do:
The data is to be shown on monthly pie charts, so what I want to be able to do is find all activites in each month where the duration is less than or equal to 1% of the monthly total for all activities, and combine them all under a new catgory, "Other".
Is this possible? I can extract the table into Excel & do it manually, but I want to automate this so it can be run by management whenever they require it.
Code:
TRANSFORM Sum(tbl_Timesheets.Duration) AS SumOfDuration
SELECT tbl_Activities.Activity
FROM tbl_Employees RIGHT JOIN (tbl_Activities RIGHT JOIN tbl_Timesheets ON tbl_Activities.Activity = tbl_Timesheets.Activity) ON tbl_Employees.SAP_HRNumber = tbl_Timesheets.SAP_HRNumber
WHERE (((tbl_Activities.Activity)<>"") AND ((tbl_Employees.Division)="NI"))
GROUP BY tbl_Activities.Activity, tbl_Employees.Division
PIVOT MonthName(Month([ActivityDate]));The data is to be shown on monthly pie charts, so what I want to be able to do is find all activites in each month where the duration is less than or equal to 1% of the monthly total for all activities, and combine them all under a new catgory, "Other".
Is this possible? I can extract the table into Excel & do it manually, but I want to automate this so it can be run by management whenever they require it.
