Crosstab Criteria Issue

[Spackle]

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:

 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.

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.

 

[CJ_London]

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")

 

[Spackle]

Thanks CJ. I'll Give this a crack.

 

[arnelgp]

you can also add another condition to return all Activiy:

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

 

[Spackle]

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.