I have created a cross tab query that has Material Numbers for the rows, with Week Numbers across the top. The problem I am having is the data contains 52 weeks worth of information. The information starts in the current week and goes out into next year. Id like my cross tab to start with the current week of this year and roll forward to the end of this year and then continue with Week 1 of next year. But the columns headings are showing Week 1 of next year and counting up to the current week of this year. The data is all out of order. Here is the SQL. Id like to Sort the PIVOT DatePart by year but not show the year in the crosstab.
TRANSFORM Sum(PLAF.QTY) AS SumOfQTY
SELECT PLAF.Material
FROM PLAF
WHERE (((PLAF.[Firm Status]) Is Null) AND ((PLAF.Material) Is Not Null))
GROUP BY PLAF.Material
PIVOT DatePart("ww",PLAF.[Finish Date]);