[RESOLVED] Access 2000 (Report): ORBER BY in a Pivot Query

Continued from here

Everything works great (Thanks Rob and Kenny) :thumb:. Now I have a new problem. The symbols in that result set are being joined to another table that has the "display friendly" name listed.

ie. In he Change Query:

Code:

TRANSFORM Sum(TEMP_tblBoardPrices.curChange) AS Change SELECT 'Change' AS Corn FROM TEMP_tblBoardPrices INNER JOIN tblBidPeriods ON TEMP_tblBoardPrices.strBoardSymbol = tblBidPeriods.strBoardSymbol WHERE (((TEMP_tblBoardPrices.strBoardSymbol) Like "C*")) GROUP BY 'Change' PIVOT tblBidPeriods.strBidPeriodName;

After the "GROUP BY" clause and before the "Pivot", I added "ORDER BY tblBidPeriods.numSortOrder ASC" and received the following:

"You tried to execute a query that does not include the specified expression 'tblBidPeriods.numSortOrder' as part of an aggregate function."

I really need this sorted by that value. Right now, the result looks like:

Code:

Corn Aug 06 Corn Cash Corn Jan 07 Corn Jan 08 Corn July 06 Corn June 06 Corn Mar 07 Corn May 07 Corn Oct 06 Corn Oct 07 Corn Oct 08 Corn Sept 06 Corn Change 2.5 2.5 3 2.5 2.5 2.5 3 2.25 2.75 3.25 2.25 2.5

As you can see, it's a pain to follow which months are in which order (cash corn is supposed to be first and the rest in ASC order. numSortOrder has the correct values if I can sort by it). :sick:

As always, extremely appreciative of any help you can provide.

Thanks. :D