Order by an aggregate function

[rdemyan via AccessMonster.com]

I can't seem to get the following SQL statement with the ORDER BY clause to
work. It does work if I order by any of the fields in the SELECT part of the
statement but not if it is the field in the TRANSFORM part.
The posted code is from the Immediate Pane:

TRANSFORM Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00') AS EUI SELECT
BUILDING_TYPE As Type, [TEMPTABLEA].BUILDING_TYPE_DESC As Description FROM
[TEMPTABLEA] GROUP BY [TEMPTABLEA].BUILDING_TYPE, [TEMPTABLEA].
BUILDING_TYPE_DESC ORDER BY Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00') ASC
PIVOT [TEMPTABLEA].CITY

The error is that I can't have an aggregate function in the ORDER BY Clause.
I tried using the alias, EUI, but that doesn't work either. So how do I
order by this.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200607/1

[DFS]

rdemyan via AccessMonster.com wrote:

I can't seem to get the following SQL statement with the ORDER BY
clause to work. It does work if I order by any of the fields in the
SELECT part of the statement but not if it is the field in the
TRANSFORM part.
The posted code is from the Immediate Pane:

TRANSFORM Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00') AS EUI SELECT
BUILDING_TYPE As Type, [TEMPTABLEA].BUILDING_TYPE_DESC As Description
FROM [TEMPTABLEA] GROUP BY [TEMPTABLEA].BUILDING_TYPE, [TEMPTABLEA].
BUILDING_TYPE_DESC ORDER BY
Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00') ASC PIVOT
[TEMPTABLEA].CITY

The error is that I can't have an aggregate function in the ORDER BY
Clause. I tried using the alias, EUI, but that doesn't work either.
So how do I order by this.

You can't, because it doesn't make sense to ORDER BY anything being
TRANSFORMed in a crosstab.

The MS warning should say something about this explicitly, because you
actually can have an agg function in the ORDER BY statement of a
non-crosstab query, ie:

SELECT BUILDING_TYPE_DESC As Description, Format(Sum(ELEC_EUI),'#,###.00')
as EUI
FROM TEMPTABLEA
GROUP BY BUILDING_TYPE_DESC
ORDER BY Format(Sum(ELEC_EUI),'#,###.00') DESC

[Allen Browne]

The ORDER BY clause has to refer to one of the fields that is a Row Heading
in the crosstab.

In any case, the Format() function outputs Text, so it would not sort
numeric values correctly. You could avoid that by creating the query without
the Format(). Then set the Format() property of the field (properties box in
query design view.)

Your query statement will be something like this:
TRANSFORM Sum(ELEC_EUI)
SELECT BUILDING_TYPE, BUILDING_TYPE_DESC
FROM TEMPTABLEA
GROUP BY BUILDING_TYPE, BUILDING_TYPE_DESC
ORDER BY BUILDING_TYPE
PIVOT CITY;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"rdemyan via AccessMonster.com" <u6836@uwewrote in message
news:63c22277637d3@uwe...

>I can't seem to get the following SQL statement with the ORDER BY clause to
work. It does work if I order by any of the fields in the SELECT part of
the
statement but not if it is the field in the TRANSFORM part.
The posted code is from the Immediate Pane:

TRANSFORM Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00') AS EUI SELECT
BUILDING_TYPE As Type, [TEMPTABLEA].BUILDING_TYPE_DESC As Description FROM
[TEMPTABLEA] GROUP BY [TEMPTABLEA].BUILDING_TYPE, [TEMPTABLEA].
BUILDING_TYPE_DESC ORDER BY Format(Sum([TEMPTABLEA].ELEC_EUI),'#,###.00')
ASC
PIVOT [TEMPTABLEA].CITY

The error is that I can't have an aggregate function in the ORDER BY
Clause.
I tried using the alias, EUI, but that doesn't work either. So how do I
order by this.