Pivot with custom header columns 1

[03Explorer]

I have a table with performance data

aID      Name     Year     Performance
1        Joe      2014     Meets Expectations
2        Mark     2015     Exceeds Expectations
3        Joe      2015     Meets Expectations
4        Jan      2015     Meets Expectations
5        Joe      2016     Meets Expectations
...      ...      ...      ...
50       Joe      2017     Meets Expectations
51       Jan      2017     Meets Expectations
52       Mark     2017     Meets Expectations

I want the data to output in a query so it is a pivot but only with the last three years (from today; so for 2018 it would display 2017, 2016, 2015).

[Code Query]TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating
SELECT tblAssociatePerformance.AssociateID
FROM tblAssociatePerformance
GROUP BY tblAssociatePerformance.AssociateID
PIVOT tblAssociatePerformance.Year;[/Code]

[Code Query-Output]AssociateID 2017 2016 2015
Joe Meets Expectations Meets Expectations Meets Expectations
Jan Meets Expectations Meets Expectations Meets Expectations
Mark Meets Expectations Meets Expectations Exceeds Expectations
[/code]


How (in Access SQL code) can I manipulate the Column header for year to be a.) Ordered Newest to Oldest Year(Left to Right) b.) Display ONLY last three years. I am thinking there should be a nested query as a filter 'SELECT Top 3 years...'

Thank you

 

[dhookom]

I would think you could use:

TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating
SELECT tblAssociatePerformance.AssociateID
FROM tblAssociatePerformance
WHERE [Year] >= Year(Date())-2
GROUP BY tblAssociatePerformance.AssociateID
PIVOT tblAssociatePerformance.Year;

If you want/need static year headings you could use a column heading of something like:

TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating
SELECT tblAssociatePerformance.AssociateID
FROM tblAssociatePerformance
GROUP BY tblAssociatePerformance.AssociateID
PIVOT "Year " & Year(Date()) - tblAssociatePerformance.Year IN ("Year 2", "Year 1" , "Year 0");

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

Awesome Duane,

When I ran the first code (which aligns with what I am trying to do) it shows only two columns (not three). I changed your Date - 2 to *3*. This shows me three years but then if I put in a current year value '2018' it now shows me 4 columns... =
It is showing me 3 years for that one person I gave 2018 value but everyone else has a blank in 2018 and the remaining 3 columns populated

[Code (SampleOutput)]
AssociateID 2018 2017 2016 2015
Joe {blank} meets meets meets
Jan meets exceeds meets {blank}
[/code]

What I am expecting is if there is a 2018 value in the dataset, the 2015 column would be dropped out of the details altogether. =
[Code (ExpectedOutput)]
AssociateID 2018 2017 2016
Joe {blank} meets meets
Jan meets exceeds meets
[/code]
*if only any Performance value holds 2018 values

Here is your code modified with what I was testing with (I also ordered the years Descending order)

TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating
SELECT tblAssociatePerformance.AssociateID
FROM tblAssociatePerformance
WHERE [Year] >= Year(Date())-3
GROUP BY tblAssociatePerformance.AssociateID
Order by tblAssociatePerformance.Year Desc
PIVOT tblAssociatePerformance.Year;

 

[dhookom]

If you don't want the current year to display then filter it out like:

[Code SQL]TRANSFORM First(tblAssociatePerformance.Rating) AS FirstOfRating
SELECT tblAssociatePerformance.AssociateID
FROM tblAssociatePerformance
WHERE [Year] Between Year(Date())-3 AND Year(Date())-1
GROUP BY tblAssociatePerformance.AssociateID
Order by tblAssociatePerformance.Year Desc
PIVOT tblAssociatePerformance.Year;[/code]

This should work unless you expect to build a report or form which would prefer static columns. If this is the case then use my earlier suggestion with the "Year "... in the column headings.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[03Explorer]

This code works great because what we are using is the independent user data and not as a whole pivot chart with everyone. So I am getting what I want (last three data points by year).

Thank you Duane!
=)

 

[dhookom]

Glad to help a fellow Twin City resident....

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016