Convert rows to columns for a Report

Question

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!

You're goig to want to take a look at the sql PIVOT statement https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx I'm afraid I can't help you much more than this because I've never actually used it myself but I'm 99% sure this does what you need — Jeremy C., May 05 '15 at 14:01
Have you considered linking that initial query to an Excel sheet and then creating a pivot table in Excel from that? Here's something I created in Excel from your initial query https://drive.google.com/file/d/0B-J5B7nFljZiMUYzZzAtTE5PeHc/view?usp=sharing This can be linked to your Access query so you can just refresh it in Excel if there's new data. If this is something you're after and not sure how to go about let me know I'll post an answer with details. — Matt Hall, May 05 '15 at 15:03
@MattHall - Can that process be automated to happen when the form loads? Basically I want the user to be able to select an employee and see that data as a reference for other actions they need to take on the form. I was planning on embedding the report into the form. — icouper, May 05 '15 at 17:21

Mike · Accepted Answer · 2015-05-05T18:10:31.877

1

You need a column for all the rating types, not an individual column for each type. If you can't redesign the table, I would suggest creating a new one for your purposes. The below uses a union to add in that type column referred to above. You create a column and hardcode the value (SelfRating, ManagerRating, etc):

SELECT * INTO EmployeeRatings
FROM (SELECT tblEmployeeYear.EmployeeId AS EmpId, ReviewYearId, "SelfRating" AS Category, SelfRating AS Score
      FROM tblEmployeeYear
      WHERE SelfRating Is Not Null
      UNION ALL
      SELECT tblEmployeeYear.EmployeeId, ReviewYearId, "ManagerRating", ManagerRating
      FROM tblEmployeeYear
      WHERE ManagerRating Is Not Null
      UNION ALL
      SELECT tblEmployeeYear.EmployeeId, ReviewYearId, "NotSelfRating", NotSelfRating
      FROM tblEmployeeYear
      WHERE NotSelfRating Is Not Null)

Then use the newly created table in place of tblEmployeeYear. Note that I use Year([ReviewDate]) which will return only the year. Also, since it looks like it may be possible to have more than one of each review type per year, I averaged the Score for the year.

TRANSFORM Avg(Score)
SELECT EmpId, Category
FROM (SELECT EmpId, Category, ReviewDate, Score
      FROM tblReviewYear 
      INNER JOIN EmployeeRatings
              ON tblReviewYear.ID = EmployeeRatings.ReviewYearID) AS Reviews
GROUP BY EmpId, Category
PIVOT Year([ReviewDate]);

edited May 05 '15 at 18:10

answered May 05 '15 at 15:43

Mike

620
11
16

HI Mike - to clarify that initial data is from a query I used to combine the data from two tables before trying to transform it into the format I need. The review years are stored in a table and then there is a table for employee data for each year. I'll edit the question to include the SQL for the base query if that helps. Maybe I just need to change my base query to make the crosstab query simpler? – icouper May 05 '15 at 17:25
@icouper It's going to be hard to avoid changing the table or recreating it I think. I updated my answer with another possible solution – Mike May 05 '15 at 18:11
Okay I think that's getting me closer. I replaced the [ReviewDate] with [YearName] for the PIVOT , since the date isn't really helpful in this case. However I was hoping to show the most recent year first - is there a way to sort the columns based on [ReviewDate] while showing [YearName] as the header? – icouper May 05 '15 at 18:54
I believe it sorts ascending (from left to right) by default, but if you want to sort it descending try adding in `ORDER BY YearName DESC` after `GROUP BY EmpId, Category` – Mike May 05 '15 at 20:50

Convert rows to columns for a Report

1 Answers1