Thread: Date Order in Crosstab Query

I have a table with a set of data and the last column has the Week Ending date i.e. 02/01/2011, 06/02/2011.

The problem I am having is that the order the dates appear in seem to range from the smallest value in the day value rather than having them in month order and then date.

Does anyone know how i can get this done?

If somebody could please help, would really appreciate it.. Thanks in advance.

Are you ordering your query by date ?
[in other words - post your SQL query]

If you are - are you sure the Dates are stored as Dates and not text ?

I confess I'm not really familiar with the TRANSFORM / PIVOT operators but I presume you're transposing your initial recordset from transaction layout of:

Manager, Friendly Queue Name, Week Date, Number of OM

to an aggregated matrix output of:

Manager, Friendly Queue Name, Week Date #1, Week Date #2, Week Date #3 etc...

with Sum of "Number of OM" aggregated under each week heading for each Manager/Queue combination ?

If that's correct are you saying the week date columns are not being generated/returned in date order (from left to right) ?

If so, does applying an ORDER BY clause in your SELECT query have any effect ?

In short I don't have an answer for you - agreed that ordering the initial data set (pre transform) has no effect - if I find something I will report back.

From what I've read it seems you need to explicitly state the ordering within the PIVOT line (ie list the headings in the sequence you wish them to appear)
in your case would appear to be an unrealistic approach

Of course the other alternative would be to modify the headers directly such that in alphabetical order they sort accordingly

TRANSFORM Sum([13WeeksData2].[Number Of OM]) AS [SumOfNumber Of OM]
SELECT [13WeeksData2].Manager, [13WeeksData2].[Friendly Queue Name], Sum([13WeeksData2].[Number Of OM]) AS [Total Number of OM]
FROM 13WeeksData2
GROUP BY [13WeeksData2].Manager, [13WeeksData2].[Friendly Queue Name]
PIVOT Format([Week Start Date],"yyyymmdd");

so at this point your week dates become yyyymmdd values and should list in ascending order.

Not in a straightforward / practical manner no (or at least NAFAIK)

From what I can tell; when using TRANSFORM/PIVOT operators the ordering of the transposed columns is A-Z at all times unless you explicitly declare / list the header values within the Query - in your case this isn't viable.

The only way (I think) you can achieve what you want via standard SQL is to ensure the query returns strings that when listed a-z will put the dates in the right order.

In this case that would mean adjusting later dates such that they become "smaller" then "earlier" dates - via subtraction etc... the resulting headers would then be pretty meaningless to the unknowing.

Example of above:

PIVOT 99999999-Format([Week Start Date],"yyyymmdd")&"^"&Format([Week Start Date],"yyyymmdd");

Your headers would become obscure (date being to right of caret) but they would be listed in descending order.

Please don't take the above as "gospel" ... I don't use Access myself (MySQL) so my knowledge of the application is limited in the extreme
so much so that I'd never seen TRANSFORM / PIVOT operators before your post

Others may be able to provide you with better alternatives.