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];
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*