Can't aggregate...

  • Thread starter mike via AccessMonster.com
  • Start date
M

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");
 
K

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