Hi, thanks for trying to explain it to me, I'll play with the relationship some more.
Here's the SQL from one query, this is for transfers from main inventory to another.
Code:
SELECT Inventory.Product, Nz([BoxesTransfered],0)*Nz([BoxQuanity],0) AS BarsTransfered, Nz([SumOfCasesTransfered],0)*Nz([BoxesPerCase],0) AS BoxesTransfered, Sum(Inventory.CasesTransfered) AS SumOfCasesTransfered, Transfers.Cooler, Product.BoxesPerCase, Product.BoxQuanity
FROM Transfers INNER JOIN (Product INNER JOIN Inventory ON Product.ProductID = Inventory.Product) ON Transfers.TransferID = Inventory.TransferID
GROUP BY Inventory.Product, Transfers.Cooler, Product.BoxesPerCase, Product.BoxQuanity
HAVING (((Transfers.Cooler)=2));
This is the SQL for the other query, orders.
Code:
SELECT Inventory.Product, Sum(Inventory.BarsGiven) AS SumOfBarsGiven, Orders.Cooler, Product.BoxQuanity, Product.BoxesPerCase, Nz([SumOfBarsGiven],0)/Nz([BoxQuanity],0) AS BoxesGiven, Nz([BoxesGiven],0)/Nz([BoxesPerCase],0) AS CasesGiven
FROM Orders, Product INNER JOIN Inventory ON Product.ProductID = Inventory.Product
GROUP BY Inventory.Product, Orders.Cooler, Product.BoxQuanity, Product.BoxesPerCase
HAVING (((Orders.Cooler)=2))
ORDER BY Inventory.Product;
Then I have a 3rd bringing the 2 together
Code:
SELECT Cooler2TransferIns.Cooler, Cooler2TransferOuts.Product, Cooler2TransferOuts.SumOfBarsGiven, Cooler2TransferOuts.BoxesGiven, Cooler2TransferOuts.CasesGiven, Cooler2TransferIns.BarsTransfered, Cooler2TransferIns.BoxesTransfered, Cooler2TransferIns.SumOfCasesTransfered, Nz([BarsTransfered],0)-Nz([SumOfBarsGiven],0) AS Bars, Nz([SumOfCasesTransfered],0)-Nz([CasesGiven],0) AS Cases, Nz([BoxesTransfered],0)-Nz([BoxesGiven],0) AS Boxes
FROM Cooler2TransferIns INNER JOIN Cooler2TransferOuts ON (Cooler2TransferIns.Cooler = Cooler2TransferOuts.Cooler) AND (Cooler2TransferIns.Product = Cooler2TransferOuts.Product)
GROUP BY Cooler2TransferIns.Cooler, Cooler2TransferOuts.Product, Cooler2TransferOuts.SumOfBarsGiven, Cooler2TransferOuts.BoxesGiven, Cooler2TransferOuts.CasesGiven, Cooler2TransferIns.BarsTransfered, Cooler2TransferIns.BoxesTransfered, Cooler2TransferIns.SumOfCasesTransfered;
I know I need the same technique for all 3 queries
I'm not using the WHERE clause