Purchases/Beginning Inventory

[leafsrock100]

I have an inventory table that contains products that come in different unit types, i.e pail, drum, tote etc.

When I run my purchase/sales queries for a certain month, some products with multiple unit types may have one type that was purchased/sold while other types are not

I.e. Product A comes in a pail and drum
and I purchase 5 pails of product A, 0 drums of product A in Jan

When I query my inventory report for Jan, the purchases are double counted; one for the drum and one for the pail. When I query the purchases, only the pail purchase comes up.

Beginning Inventory SQL:

SELECT [Inventory Transactions].ProductID, Sum(nz([QtyReceived],0)-nz([QtyShipped],0)-nz([QtyShrinkage],0)) AS [Beginning Inventory], IIf([Beginning Inventory]=0,0,Format(Sum(nz([RawUnitPrice]*[QtyReceived],0))/Sum(nz([QtyReceived],0)),"$0.00")) AS BegUnitPrice, Units.UnitType
FROM [Inventory Transactions] INNER JOIN Units ON [Inventory Transactions].UnitID = Units.UnitID
WHERE ((([Inventory Transactions].TransactionDate)<=[Forms]![Filtered Inventory Summary/Sales Report Date Range]![BeginDate]))
GROUP BY [Inventory Transactions].ProductID, Units.UnitType;


Purchases SQL:

SELECT [Inventory Transactions].ProductID, Sum(nz([QtyOrdered],0)) AS Purchases, IIf([Purchases]=0,0,Format(Round(Sum(nz([RawUnitPrice]*[QtyOrdered],0))/Sum(nz([QtyOrdered],0)),2),"$0.00")) AS PurchasePrice
FROM ([Inventory Transactions] INNER JOIN Products ON [Inventory Transactions].ProductID = Products.ProductID) LEFT JOIN Units ON [Inventory Transactions].UnitID = Units.UnitID
WHERE ((([Inventory Transactions].TransactionDate)>=[Forms]![Filtered Inventory Summary/Sales Report Date Range]![BeginDate] And ([Inventory Transactions].TransactionDate)<=[Forms]![Filtered Inventory Summary/Sales Report Date Range]![EndDate]))
GROUP BY [Inventory Transactions].ProductID;


Report SQL:

SELECT DISTINCTROW Products.ProductID, Products.ProductName, [Beginning Inventory].UnitType, [Beginning Inventory].[Beginning Inventory], Purchases.Purchases, Sales.Sales, nz([Beginning Inventory],0)+nz([Purchases],0)-nz([Sales],0) AS [Ending Inventory], [Beginning Inventory].BegUnitPrice, Format(nz([Beginning Inventory]*[BegUnitPrice],0),"$0.00") AS BegTotal, Format(Sum(nz([Beginning Inventory]*[BegUnitPrice],0)+nz([Purchases]*[PurchasePrice],0))/Sum(nz([Purchases],0)+nz([Beginning Inventory],0)),"$0.00") AS [Ending Unit Price], Format(nz([Ending Unit Price]*[Ending Inventory],0),"$0.00") AS [End Total]
FROM (Units INNER JOIN (((Products INNER JOIN [Inventory Transactions] ON Products.ProductID = [Inventory Transactions].ProductID) INNER JOIN [Beginning Inventory] ON Products.ProductID = [Beginning Inventory].ProductID) INNER JOIN Purchases ON Products.ProductID = Purchases.ProductID) ON Units.UnitID = [Inventory Transactions].UnitID) INNER JOIN Sales ON Products.ProductID = Sales.ProductID
GROUP BY Products.ProductID, Products.ProductName, [Beginning Inventory].UnitType, [Beginning Inventory].[Beginning Inventory], Purchases.Purchases, Sales.Sales, [Beginning Inventory].BegUnitPrice;


Any help would be greatly appreciated,