Using instructions found here I've tried to create a crosstab query to show historical data from three previous years and I would like to output it in a report.
I've got a few complications that are making this difficult and I'm having trouble getting the data to show correctly.
The query it is based on is structured like this:
EmpID | ReviewYearID | YearName | ReviewDate | SelfRating | ManagerRating | NotSelfRating |
1 | 5 | 2013 | 01/09/2013 | 3.5 | 3.5 | 3.5 |
1 | 6 | 2014 | 01/09/2014 | 2.5 | 2.5 | 2.5 |
1 | 7 | 2015 | 01/09/2015 | 4.5 | 4.5 | 4.5 |
2 | 6 | 2014 | 01/09/2014 | 2.0 | 2.0 | 2.0 |
2 | 7 | 2015 | 01/09/2015 | 2.0 | 2.0 | 2.0 |
3 | 7 | 2015 | 01/09/2015 | 5.0 | 5.0 | 5.0 |
[Edit]: Here is the SQL for the base query. It is combining data from two tables:
SELECT tblEmployeeYear.EmployeeID AS EmpID, tblReviewYear.ID AS ReviewYearID, tblReviewYear.YearName, tblReviewYear.ReviewDate, tblEmployeeYear.SelfRating, tblEmployeeYear.ManagerRating, tblEmployeeYear.NotSelfRating
FROM tblReviewYear INNER JOIN tblEmployeeYear ON tblReviewYear.ID = tblEmployeeYear.ReviewYearID;
[/Edit]
I would like a crosstab query that transposes the columns/rows to show historical data for up to 3 previous years (based on review date) for a specific employee. The end result would look something like this for Employee ID 1:
Year | 2015 | 2014 | 2013 |
SelfRating | 4.5 | 2.5 | 3.5 |
ManagerRating | 4.5 | 2.5 | 3.5 |
NotSelfRating | 4.5 | 2.5 | 3.5 |
Other employees would have less columns since they don't have data for previous years.
I'm having issues with filtering it down to a specific employee and sorting the years by their review date (the name isn't always a reliable way to sort them).
In the end I'm looking to use this as the data for a report.
If there is a different way than a crosstab query to accomplish this I would be okay with that as well.
Thanks!