Can't aggregate...

[mike via AccessMonster.com]

hi,
i got an error can not aggregate function in order by clause.... when i try
to sort in plIssQty

any help is appreciated.

TRANSFORM Count(RecurringItems.plIssQty) AS CountOfplIssQty
SELECT RecurringItems.MRN_No, Last(RecurringItems.NSN) AS LastOfNSN, Last
(RecurringItems.Unit_price) AS LastOfUnit_price, Last(RecurringItems.PartNo)
AS LastOfPartNo, Last(RecurringItems.plIssPartID) AS LastOfplIssPartID, Last
(RecurringItems.Description) AS LastOfDescription, Last(RecurringItems.UOM)
AS LastOfUOM, Count(RecurringItems.plIssQty) AS [Total No Of Issue], Sum
(RecurringItems.plIssQty) AS IssTot, Avg(RecurringItems.plIssQty) AS Ave,
StDev(RecurringItems.plIssQty) AS StDev
FROM RecurringItems
GROUP BY RecurringItems.MRN_No
PIVOT Format([pIssDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul",
"Aug","Sep","Oct","Nov","Dec");

 

[KARL DEWEY]

When you count, sum, and average it rolls the items up so they will not be
listed and cannot sort.

You can sort the count, sum, or average.