Full Version: math problem w/ a query
maglinvinn
the SQL:
SELECT Products.ProductID, Products.ItemID, Products.Description, Products.[QTY Reorder], Sum(ProductLocation.Quantity) AS SumOfQuantity
FROM Products INNER JOIN ProductLocation ON Products.ProductID = ProductLocation.ProductID
GROUP BY Products.ProductID, Products.ItemID, Products.Description, Products.[QTY Reorder];

table structure

****Products****

ProductID (PK)
ItemID
CategoryID
Description
Retail
QTY Reorder
Receiptname

****ProductLocation****
ProductLocationID
ProductID (FK)
LocationID
Quantity

alright. this form needs to total all quanities of each item and result a number, which it does, the SUM() feature.

now. i need the query to only select recrods where the sum is less than the QTY Reorder, and to show by how much less.

IE - if Product ABC has a QTY of "10" and all locations combined only have a total of 8 items, i need to see that entry, with '2' listed.

There will be a 'Reorder to' field later, which is to say that when we have to reorder the items we get say enough to put say 100 back into inventory * in this scenario, having to order/make 92 items to fullfil inventory needs*

But understanding the methods behind getting the '2' to print will be enough i imagine to do just about anything i need. i ASSUME its going to require a sub level sql like i learned a bit of in 9i, but for the life of me i ain't got a clue how to get Access to do it. *grin*
maglinvinn
just as an attempt, its been a year ish since the last time i did straight SQL:

SELECT Products.ProductID, Products.ItemID, Products.Description, Products.[QTY Reorder], Sum(ProductLocation.Quantity) AS SumOfQuantity,

Products.[QTY Ideal] - (Select SUM(ProductLocation.Quantity) from ProductLocation group by Products.productID) AS TotalRetail

FROM Products INNER JOIN ProductLocation ON Products.ProductID = ProductLocation.ProductID
where Products.[QTY Reorder] > (Select SUM(ProductLocation.Quantity) from ProductLocation group by Products.productID)

GROUP BY Products.ProductID, Products.ItemID, Products.Description, Products.[QTY Reorder];

or something like that. lol. god i think i need a beer.
maglinvinn
anyone?
maglinvinn
bringing this back up... still need a solution
truittb
Try this untested aircode:

CODE
Select P.ProductID
, P.ItemID
, P.CategoryID
, P.Description
, P.Retail
, P.[QTY Reorder]
, P.Receiptname
From Products P
Where P.ProductID In (Select ProductID
From ProductLocation
WHERE ProductID = P.ProductID
Group By ProductID
, LocationID
Having Sum(Quantity) <= P.[QTY Reorder])
maglinvinn
thank you i'll start experimenting with it immediately.