Link to home
Start Free TrialLog in
Avatar of StevieGuy

asked on

Order Access Query by Subquery result

The query below has stumped me for hours and hours. I've tried every possible thing I could find online. If anyone can help I would most grateful!

The query uses a subquery to list how much product a customer has used and it works fine. However I would like to order the query by the usage result. No go no matter what I try.

SELECT TOfferItem.ID AS OfferID, TOfferItem.FKManfItemID, tblManfItems_m.Manf_Code AS Code, tblManfItems_m.Short_Desc, tblCompany_m.CompanyName AS Manufacturer, tblManfItems_m.Target_Ounce_Size AS Target_Size_Oz, qryManfItemFileCount.File_Count, tblGeneralItems.LongDesc AS Item_Group, tblManfItems_m.Quality AS Quality, tblItemPacks.PackDesc AS Pack, [Quantity]*[weight] AS Avail_Weight, TOfferItem.Price AS Cost, tblCity.City, tblStates.StateAbbr, TOfferItem.Note AS Offer_Note, (SELECT Sum(PODtl.TotalLbl) AS Total_Usage
FROM tblManfItems INNER JOIN (tblOrderStatus INNER JOIN (PoHDR INNER JOIN PODtl ON PoHDR.TranID = PODtl.TranID) ON tblOrderStatus.OrderID = PoHDR.TranID) ON tblManfItems.ID = PODtl.FKManfItemID
GROUP BY tblManfItems.ID, PoHDR.Customer, tblOrderStatus.Submitted, tblOrderStatus.Canceled
HAVING (((tblManfItems.ID)= tblManfItems_m.ID) AND ((PoHDR.Customer)=1319) AND ((tblOrderStatus.Submitted)=True) AND ((tblOrderStatus.Canceled)=False))) AS Total_Usage
FROM qryManfItemFileCount RIGHT JOIN (tblCompany AS tblCompany_m RIGHT JOIN (tblStates RIGHT JOIN (tblCity RIGHT JOIN (tblGeneralItems RIGHT JOIN (tblItemPacks RIGHT JOIN (tblOfferItemInventory AS TOfferItem INNER JOIN tblManfItems AS tblManfItems_m ON TOfferItem.FKManfItemID = tblManfItems_m.ID) ON tblItemPacks.ID = tblManfItems_m.Pack) ON tblGeneralItems.ItemID = tblManfItems_m.Commodity_ID) ON tblCity.CityID = TOfferItem.FKCityID) ON tblStates.StateID = tblCity.State) ON tblCompany_m.PKCompanyID = tblManfItems_m.FK_Company_ID) ON qryManfItemFileCount.ManfID = TOfferItem.FKManfItemID
WHERE ((([Quantity]*[weight])>0));

Open in new window

Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of StevieGuy


You are amazing. What was I doing wrong?