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

[sevenhalo]

Continued from here

Everything works great (Thanks Rob and Kenny) . 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:

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:

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).

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

Thanks.

[sevenhalo]

I'm being stupid.

I can bring those values over when I requery the temp table.

My bad, sorry.