Help with SQL Inventory query (1 Viewer)

[bwarren]

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.

 

[plog]

First, I don't think your query is correct, your GROUP BY has too many fields. Why are you grouping by LBsPerCase and QuantityCases? When you run it do you see "duplicates"?

As for your questions, we need to see your table structure. The best way to do this is to complete the Relationship Tool in Access, expand every table so we can see all fields, take a screenshot then post it back here. If that's too much, post a copy of your database.

 

[bwarren]

Yes, I would agree I think GROUP BY has too many fields. When I did not have QuantityCases and LBsPerCase in that section, I was getting an error saying the formula that included them were not part of an aggregate function. In query design, I just checked them to not 'show'. I thought that because I was retrieving LBsPerCase from the Inventory table, that I needed to include it in the query but that may be an incorrect way of doing it.

 

[ebs17]

Your data model is getting in your way. I would never save a stock because it is the sum of receipts (positive quantity) and issues (negative quantity) and is therefore a calculated value that also changes with each movement.

The values for receipts and issues should be stored in a field in a movement table. With the help of the time stamp, you can determine total receipts, total issues and stocks at any point in time, with very simple queries.