SELECT [Order Details].ProductID, Sum([Order Details].QuantityCases) AS QOH, Inventory.LBsPerCase*[Order Details].QuantityCases AS totWeight, Inventory.IngredientName
FROM Inventory INNER JOIN [Order Details] ON Inventory.ProductID = [Order Details].ProductID
GROUP BY [Order Details].ProductID, Inventory.IngredientName, Inventory.LBsPerCase, [Order Details].QuantityCases;
This is the current query I have for my inventory tracking database where the stockIN is totaled and given back as QOH and total weight as well. I have made a sales order table and salesOrderDetails table to be able to bring stockOUT of the inventory but I am having trouble finding the correct syntax to
1. sum up the outgoing transactions (sales orders) ------------> I read that I must multiply outgoing quantities by (-1) to make them negative but nothing I have tried so far has worked
2. sum up the total weight based on (incoming stock weight - outgoing stock weight)
If anyone could point me in the right direction, it would be greatly appreciated as my SQL knowledge is quite limited.
FROM Inventory INNER JOIN [Order Details] ON Inventory.ProductID = [Order Details].ProductID
GROUP BY [Order Details].ProductID, Inventory.IngredientName, Inventory.LBsPerCase, [Order Details].QuantityCases;
This is the current query I have for my inventory tracking database where the stockIN is totaled and given back as QOH and total weight as well. I have made a sales order table and salesOrderDetails table to be able to bring stockOUT of the inventory but I am having trouble finding the correct syntax to
1. sum up the outgoing transactions (sales orders) ------------> I read that I must multiply outgoing quantities by (-1) to make them negative but nothing I have tried so far has worked
2. sum up the total weight based on (incoming stock weight - outgoing stock weight)
If anyone could point me in the right direction, it would be greatly appreciated as my SQL knowledge is quite limited.