Crosstab Criteria Issue

Spackle

Registered User.
Local time
, 09:51
Joined
Jul 12, 2018
Messages
12
I've trying to produce a crosstab query with employee numbers as the row headings, billed activities for the columns, and duration sums as the values. When I run the code below, the output is correct and shows all activities, even activities with no hours billed to them:

Code:
 TRANSFORM Format(Sum(tbl_Timesheets.Duration),"Fixed") AS SumOfDuration
SELECT tbl_Timesheets.SAP_HRNumber
FROM tbl_Activities LEFT JOIN tbl_Timesheets ON tbl_Activities.Activity = tbl_Timesheets.Activity
GROUP BY tbl_Timesheets.SAP_HRNumber
PIVOT tbl_Activities.Activity;
However, when I add criteria to limit the date range as below, the query stops returning all activities, and only returns those with records.

Code:
TRANSFORM Format(Sum(tbl_Timesheets.Duration),"Fixed") AS SumOfDuration
SELECT tbl_Timesheets.SAP_HRNumber
FROM tbl_Activities LEFT JOIN tbl_Timesheets ON tbl_Activities.Activity = tbl_Timesheets.Activity
WHERE (((Month([ActivityDate]))=8) AND ((Year([ActivityDate]))=2018))
GROUP BY tbl_Timesheets.SAP_HRNumber
PIVOT tbl_Activities.Activity;
How do I tell Access to continue to return ALL activity column headings? I'm sure that this question has been asked before, but I can't see how to apply the answers to my specific code.
 
right click in the query design window and select properties or select properties from the ribbon - populate the column headings property with the headings you want to see, separated by a commas.

Or in the sql change this line

PIVOT tbl_Activities.Activity

to

PIVOT tbl_Activities.Activity IN ("Activity1","Activity2")
 
Thanks CJ. I'll Give this a crack.
 
you can also add another condition to return all Activiy:
Code:
TRANSFORM Format(Sum(IIf(Month(ActivityDate)=8 And Year(ActivityDate)=2018,tbl_Timesheets.Duration)),"Fixed") AS SumOfDuration
SELECT tbl_Timesheets.SAP_HRNumber
FROM tbl_Activities LEFT JOIN tbl_Timesheets ON tbl_Activities.Activity = tbl_Timesheets.Activity
GROUP BY tbl_Timesheets.SAP_HRNumber
PIVOT tbl_Activities.Activity
 
Last edited:
Thanks guys. Got it working using CJ's method, but Arnelgp's method looks more flexible for if I need to add or change activities. I'll keep that one in the back of my mind for the next major re-write.
 

Users who are viewing this thread

Back
Top Bottom