Solved Sort order in a Cross-tab query...

[Garren.Shannon]

Hey all,

I have a cross-tab query (MileageBreakoutByVehicleByCode) that I crafted from a filter query (MileageBreakoutByVehicle includes the Trips and Vehicle tables). The filter is sorted by vehicle ID which puts the data in a nice ordered format as such. VehicleID is from the Vehicle table.

 

[theDBguy]

Was there a question?

 

[Garren.Shannon]

Crap... not sure what happened to that post but only half of it is there?!?

The question is I can't figure out how to get the crosstab view to sort by vehicleID rather than alpha-numeric. The SQL for the parent query is

SELECT Trip.[T-Date], Vehicles.VehicleName, [Trans-Type].Code, Trip.MaxCount, Trip.OD_Depart, Trip.OD_Return, [OD_Return]-[OD_Depart] AS [Total Miles]
FROM SchoolYrDates, Vehicles INNER JOIN ([Trans-Type] INNER JOIN Trip ON [Trans-Type].CodeID = Trip.CodeID) ON Vehicles.VehicleID = Trip.VehicleID
WHERE (((Trip.[T-Date]) Between [SchoolYrDates]![SchoolYRStart] And [SchoolYrDates]![SchoolYrEnd]) AND ((SchoolYrDates.CurrentYear)=True))
ORDER BY Vehicles.VehicleID;

The crosstab SQL is

TRANSFORM Count(MileageBreakoutByVehicleQ.MaxCount) AS CountOfMaxCount
SELECT MileageBreakoutByVehicleQ.VehicleName, Sum(MileageBreakoutByVehicleQ.[Total Miles]) AS [SumOfTotal Miles]
FROM MileageBreakoutByVehicleQ
GROUP BY MileageBreakoutByVehicleQ.VehicleName
PIVOT MileageBreakoutByVehicleQ.Code;

In the query, the view is by ID but it does not seem to act the same in the crosstab.

 

[Garren.Shannon]

MialageBreakoutByVehicleQ is the first SQL code above.

 

[June7]

Review http://allenbrowne.com/ser-67.html

I don't see VehicleID included in the CROSSTAB. Would have to include in the SELECT and GROUP BY clauses.

 

[Garren.Shannon]

As in below?

TRANSFORM Count(MileageBreakoutByVehicleQ.MaxCount) AS CountOfMaxCount
SELECT MileageBreakoutByVehicleQ.VehicleName, MileageBreakoutByVehicleQ.VehicleID Sum(MileageBreakoutByVehicleQ.[Total Miles]) AS [SumOfTotal Miles]
FROM MileageBreakoutByVehicleQ
GROUP BY MileageBreakoutByVehicleQ.VehicleID
PIVOT MileageBreakoutByVehicleQ.Code;

 

[June7]

And what happened when you tried to open?

 

[Garren.Shannon]

Syntax error initially. But you led me in the right direction. It is now working.