Messy aggregate Query

[Javamahn]

I am out of ideas and hoping this query can be accomplished. I have 2 tables, Products and ProductTransactions. They are relational via the ProductID field. The current Query reads: SELECT Sum ([ProductTransactions].[recieved] - [ProductTransactions].[sold])As OnHand, [Products].[SerialNumber] FROM Products INNER JOIN ProductTransactions ON [Products].[ProductID]=[ProductTransactions].[ProductID] GROUP BY [Products].[SerialNumber]. this gives me the current OnHand and Serial Number for all Products. What I want is a filter which returns only those records for which the OnHand for a Specific product is <= [Products].[ReorderLevel] for that product. Is this possible. I get nothing but errors when I place the &quot;Where&quot; clause anywhere in the above. I hope my explanation is clear. Any help would sure be appreciated.

Thanks

 

[JerryDennison]

You'll need to use two queries to accomplish this. The one you already have. Insure you include the ProductID in this query but not the Products table. Use this query as the basis for your second one along with the Poducts Table. You'll then be able to add the WHERE clause to give you those Products who've reached or exceeded the re-order level.

 

[MichaelRed]

ProdId SerNum ReOrdLvl
123456 843166 30
45678 aksjdn 20
159258 askenr 500
asdfg *&^*&^ 25
'_______________________________

ProdId Rcvd Sold
123456 200 180
45678 40 0
159258 4000 3850
asdfg 38 12
'_______________________________
ProdId SerNum ReOrdLvl Rcvd Sold OnHand
123456 843166 30 200 180 20
159258 askenr 500 4000 3850 150

'_______________________________
SELECT tblProd.ProdId, tblProd.SerNum, tblProd.ReOrdLvl, tblTrans.Rcvd, tblTrans.Sold, [Rcvd]-[Sold] AS OnHand
FROM tblTrans INNER JOIN tblProd ON tblTrans.ProdId = tblProd.ProdId
WHERE ((([Rcvd]-[Sold])<=[ReOrdLvl]));
'________________________________________________________



I (rather Obviously) took some liberties with the table and field names. Since it is SO OBVIOUS, you will be able to EASILY decode my names to your names and proceed.



MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Javamahn]

Will this account for the fact that in tblTrans there will be multiple transactions both receiving and sold that are attached to the product by ID? I thought I would have to use a SUM aggregate in that case.

 

[MichaelRed]

Sorry, I missed the SUN. Try it this way.

SELECT tblProd.ProdId, tblProd.SerNum, tblProd.ReOrdLvl, Sum(tblTrans.Rcvd) AS Rcvd, Sum(tblTrans.Sold) AS Sold, [Rcvd]-[Sold] AS OnHand
FROM tblTrans INNER JOIN tblProd ON tblTrans.ProdId = tblProd.ProdId
GROUP BY tblProd.ProdId, tblProd.SerNum, tblProd.ReOrdLvl, [Rcvd]-[Sold]
HAVING ((([Rcvd]-[Sold])<=[ReOrdLvl]));

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over