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*

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.

anyone?

bringing this back up... still need a solution

Try this untested aircode:

thank you i'll start experimenting with it immediately.