MS Access Pivoting Query Result Rows into Columns-CodePudding

I'm new to working with MS-Access reports. I have a query that returns the results of employees timesheets, grouped by both month and paycode. I'd like to make a report showing the the following

Employee	OT Shifts Jan	Regular Shifts Jan	OT Shifts Feb	Regular Shifts Feb
1234	1	1	2	1
5678	5	2	1	0

However my query is formatted as:

Employee	Month	Shift	Paycode
1234	Jan	1	OT
1234	Jan	1	Regular
1234	Feb	2	OT
1234	Feb	1	Regular
5678	Jan	5	OT
5678	Jan	2	Regular
5678	Feb	1	OT
5678	Feb	0	Regular

Can a field on a report be conditionally told to reference a specific "Where clause" so that I can move the fields around at will or do I need to reform my query to be able to do this?

For reference my Query code is:

SELECT tblAssignedEmployees.EmployeeID, Format(Date_In,"yyyy-mm") AS [Month], Count(tblShift.Date_In) AS ShiftsPerEmployee, tblAssignedEmployees.PayCode
FROM tblShift INNER JOIN tblAssignedEmployees ON tblShift.ShiftNum = tblAssignedEmployees.ShiftNum
GROUP BY tblAssignedEmployees.EmployeeID, Format(Date_In,"yyyy-mm"), tblAssignedEmployees.PayCode;

CodePudding user response：

Consider conditional aggregation:

SELECT e.EmployeeID
     , SUM(IIF(MONTH(s.Date_In) = 1 AND e.PayCode = 'OT', 1, 0) AS [OT Shifts Jan]
     , SUM(IIF(MONTH(s.Date_In) = 1 AND e.PayCode = 'Regular', 1, 0) AS [Regular Shifts Jan]
     , SUM(IIF(MONTH(s.Date_In) = 2 AND e.PayCode = 'OT', 1, 0) AS [OT Shifts Feb]
     , SUM(IIF(MONTH(s.Date_In) = 2 AND e.PayCode = 'Regular', 1, 0) AS [Regular Shifts Feb]
FROM tblShift s
INNER JOIN tblAssignedEmployees e
   ON s.ShiftNum = e.ShiftNum
GROUP BY e.EmployeeID

Even shorter without IIF, sum the True conditions but multiply by -1 since Access treats True as -1 and False as 0.

SELECT e.EmployeeID
     , SUM(MONTH(s.Date_In) = 1 AND e.PayCode = 'OT') * -1 AS [OT Shifts Jan]
     , SUM(MONTH(s.Date_In) = 1 AND e.PayCode = 'Regular') * -1 AS [Regular Shifts Jan]
     , SUM(MONTH(s.Date_In) = 2 AND e.PayCode = 'OT') * -1 AS [OT Shifts Feb]
     , SUM(MONTH(s.Date_In) = 2 AND e.PayCode = 'Regular') * -1 AS [Regular Shifts Feb]
FROM tblShift s
INNER JOIN tblAssignedEmployees e
   ON s.ShiftNum = e.ShiftNum
GROUP BY e.EmployeeID