different sorting order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Attached is my SQL. Any idea what I need to change for the order to sort by Carrier before Delivery Cost

Thanks

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS T
SELECT tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal] AS Goal, tblCarriers.[Carrier Code] AS Carrier, tblDeliveries.[Delivery Cost] AS Cos
FROM tblDestinations INNER JOIN (tblCarriers INNER JOIN tblDeliveries ON tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID]) ON tblDestinations.[Location ID] = tblDeliveries.[Location ID
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending Date])
GROUP BY Format(tblDeliveries.[Delivery Date],"yyyymm"), tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal], tblCarriers.[Carrier Code], tblDeliveries.[Delivery Cost
ORDER BY tblDestinations.State, tblDestinations.Cit
PIVOT Format([Delivery Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
 
Rawley said:
Attached is my SQL. Any idea what I need to change for the order to sort by Carrier before Delivery Cost?

Thanks!

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS Tr
SELECT tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal] AS Goal, tblCarriers.[Carrier Code] AS Carrier, tblDeliveries.[Delivery Cost] AS Cost
FROM tblDestinations INNER JOIN (tblCarriers INNER JOIN tblDeliveries ON tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID]) ON tblDestinations.[Location ID] = tblDeliveries.[Location ID]
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending Date]))
GROUP BY Format(tblDeliveries.[Delivery Date],"yyyymm"), tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal], tblCarriers.[Carrier Code], tblDeliveries.[Delivery Cost]
ORDER BY tblDestinations.State, tblDestinations.City
PIVOT Format([Delivery Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Click to expand...

ORDER BY tblDestinations.State, tblDestinations.City,
tblCarriers.[Carrier Code], tblDeliveries.[Delivery Cost]
 
Rawley said:
Attached is my SQL. Any idea what I need to change for the order to sort
Click to expand...
by Carrier before Delivery Cost?
Thanks!

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS Tr
SELECT tblDestinations.State, tblDestinations.City,
Click to expand...
tblDestinations.[Freight Goal] AS Goal, tblCarriers.[Carrier Code] AS
Carrier, tblDeliveries.[Delivery Cost] AS Cost
FROM tblDestinations INNER JOIN (tblCarriers INNER JOIN tblDeliveries ON
Click to expand...
tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID]) ON
tblDestinations.[Location ID] = tblDeliveries.[Location ID]
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending Date]))
GROUP BY Format(tblDeliveries.[Delivery Date],"yyyymm"),
Click to expand...
tblDestinations.State, tblDestinations.City, tblDestinations.[Freight Goal],
tblCarriers.[Carrier Code], tblDeliveries.[Delivery Cost]
ORDER BY tblDestinations.State, tblDestinations.City
PIVOT Format([Delivery Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
Click to expand...

Try the following and see if it works:

TRANSFORM Count(tblDeliveries.[Delivery ID]) AS Tr
SELECT tblDestinations.State
,tblDestinations.City
,tblDestinations.[Freight Goal] AS Goal
,tblCarriers.[Carrier Code] AS Carrier
,tblDeliveries.[Delivery Cost] AS Cost
FROM tblDestinations
INNER JOIN
(tblCarriers
INNER JOIN
tblDeliveries
ON tblCarriers.[Carrier ID] = tblDeliveries.[Carrier ID])
ON tblDestinations.[Location ID] = tblDeliveries.[Location ID]
WHERE (((tblDeliveries.[Delivery Date]) Between [Start Date] And [Ending
Date]))
GROUP BY Format(tblDeliveries.[Delivery Date],"yyyymm")
,tblDestinations.State
,tblDestinations.City
,tblDestinations.[Freight Goal]
,tblCarriers.[Carrier Code]
,tblDeliveries.[Delivery Cost]
ORDER BY tblDestinations.State
,tblDestinations.City
,tblCarriers.[Carrier Code]
PIVOT Format([Delivery Date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 
Back
Top