Ordering Columns Cross Tab Query

[CarlRostron]

I cannot quite get my head around this.

I have a cross tab query. Essentially it groups together posted volumes into week numbers for different offices.

However, when I run the query, the order of the columns is not in a logical number order. I get Week 1 then Week 10 then Week 11 and Week 2 is further down the list and then Week 20 comes after that.

I would like if at all possible the Week Numbers to follow after one another i.e. Week 1 first then up to Week 52 in correct number order.

In my Dates Table I do have a SortID column which I hoped would resolve this issue so I could sort on the SortID column however this fails to work.

Attached is the query if anyone can help with this please.

PARAMETERS [Forms]![frmSumOfVolByCCAndFormat]![cmbOfficeSearch] Text ( 255 ), Forms![frmSumOfVolByCCAndFormat]![txtStartDate] DateTime, Forms![frmSumOfVolByCCAndFormat]![txtEndDate] DateTime;
TRANSFORM Sum(tblTrafficEast.TrafficVolume) AS SumOfTrafficVolume
SELECT tblOffice.CostCentre, tblOffice.OfficeName, tblTrafficFormat.Format, Sum(tblTrafficEast.TrafficVolume) AS [Total Volume]
FROM tblTrafficFormat INNER JOIN (tblOffice INNER JOIN (tblDates INNER JOIN tblTrafficEast ON tblDates.DateID = tblTrafficEast.DateID) ON tblOffice.OfficeID = tblTrafficEast.OfficeID) ON tblTrafficFormat.TrafficFormatID = tblTrafficEast.TrafficFormatID
WHERE (((tblDates.theDate) Between [Forms]![frmSumOfVolByCCAndFormat]![txtStartDate] And [Forms]![frmSumOfVolByCCAndFormat]![txtEndDate]) AND ((tblOffice.CostCentre)=[Forms]![frmSumOfVolByCCAndFormat]![cmbOfficeSearch]))
GROUP BY tblOffice.CostCentre, tblOffice.OfficeName, tblTrafficFormat.Format
PIVOT "Week " & [tblDates].[WeekNo];

(I can post whatever you need to help me fix this but wasn't sure what code / tables etc to post up)

Thanks

 

[JHB]

Because it is text formatted, try by adding 0 for weeks lesser as 10, so it wil be 01, 02, 03 etc. Code not tested.

... PIVOT "Week " & Format([tblDates].[WeekNo],"00")

Else post a stripped version of your database with some sample data, (zip it).

 

[CarlRostron]

You made that look far too easy

Solved. Thanks for your help...

 

[JHB]

You're welcome, luck with you project.