Solved

Order Access Query by Subquery result

Posted on 2013-05-10
2
674 Views
Last Modified: 2013-05-10
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

0
Comment
Question by:StevieGuy
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39157636
try this
SELECT * 
  FROM (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 ))) T1 
 ORDER BY Total_Usage; 

Open in new window

0
 

Author Closing Comment

by:StevieGuy
ID: 39157648
You are amazing. What was I doing wrong?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question