Cross Table and DatePart

[simon03]

Hi,

I have this cross table for a query called OUTPUT. The query has four fields (country, indicator, value and date). I want to cross table by date as follows:

TRANSFORM Sum(OUTPUT.value)
SELECT OUTPUT.country, OUTPUT.Indicator
FROM OUTPUT
GROUP BY OUTPUT.country, OUTPUT.Indicator
PIVOT OUTPUT.[date];

This works fine but I'd like to visualise the date formatted by quarters instead of dd/mm/yyyy. I have modified my SQL code as follows:

TRANSFORM Sum(OUTPUT.value)
SELECT OUTPUT.country, OUTPUT.Indicator
FROM OUTPUT
GROUP BY OUTPUT.country, OUTPUT.Indicator
PIVOT "Q" & DatePart("q",OUTPUT.[date]) & " " & Year(OUTPUT.[date]);

My problem is that using the DatePart and Year functions I miss the sorting.

Is there any approach that allows me to keep the date sorted in the cross table?

I have also tried to include the date formatting in the OUTPUT query and to sort the date there. It works fine there but when I create the cross table I again loose the sorting.

Thanks,
Simone

 

[JHB]

Put the year first instead of last.

 

[simon03]

You got it!

Thanks for your help!

Simone