Home > Software design >  MS Access Pivoting Query Result Rows into Columns
MS Access Pivoting Query Result Rows into Columns

Time:12-03

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
  • Related