Hello:
I am developing a check In/Out system. when a check out occurs, stock is decremented by the amount that is checked out, and then incremented when that product is checked back in. I have a query that determines that amount of a product that is in stock.
SELECT [Check InOut].checkOutInID, [Check InOut].checkOutQuantity, [AcqDetail Query].StockTake.productID, [AcqDetail Query].SumOfstockQuantity, (([AcqDetail Query].[SumOfacqQuantity])-[Check InOut].[checkOutQuantity]) AS Expr1
FROM (Products INNER JOIN [AcqDetail Query] ON Products.[productID] = [AcqDetail Query].[AcqDetail].[productID]) INNER JOIN [Check InOut] ON Products.[productID] = [Check InOut].[productID];
This query however, does not calculate past this point. It gives me the incorrect output for Expr1 (quantity on hand), if a product is checked out more than once, as opposed to decrementing from the new stock amount, it deducts from the original one. For instance:
checkOutInID/ Item/ Quantity Out / Product/ SumOfstock/ Expr1
73/ Hammer/ 1/ Hammer/ 7/ 6
74/ "1 1/2"" Putty Knife"/ 1/ "1 1/2"" Putty Knife"/ 2/ 1
75/ Hammer/ 2/ Hammer/ 7/ 5
In record 75, Expr1 should read 4 and SumOfstock should read 6, because in record 73, after the initial hammer was checked out, there were 6 left. My plan is that when an item is checked back in, the stock will increase by the number of items added back to stock. I have been unable to achieve this. Additionally, how will I make it so that when something is checked out stock is decremented, but when it is checked baack in, stock is incremented? Any insight will be appreciated.
I am developing a check In/Out system. when a check out occurs, stock is decremented by the amount that is checked out, and then incremented when that product is checked back in. I have a query that determines that amount of a product that is in stock.
SELECT [Check InOut].checkOutInID, [Check InOut].checkOutQuantity, [AcqDetail Query].StockTake.productID, [AcqDetail Query].SumOfstockQuantity, (([AcqDetail Query].[SumOfacqQuantity])-[Check InOut].[checkOutQuantity]) AS Expr1
FROM (Products INNER JOIN [AcqDetail Query] ON Products.[productID] = [AcqDetail Query].[AcqDetail].[productID]) INNER JOIN [Check InOut] ON Products.[productID] = [Check InOut].[productID];
This query however, does not calculate past this point. It gives me the incorrect output for Expr1 (quantity on hand), if a product is checked out more than once, as opposed to decrementing from the new stock amount, it deducts from the original one. For instance:
checkOutInID/ Item/ Quantity Out / Product/ SumOfstock/ Expr1
73/ Hammer/ 1/ Hammer/ 7/ 6
74/ "1 1/2"" Putty Knife"/ 1/ "1 1/2"" Putty Knife"/ 2/ 1
75/ Hammer/ 2/ Hammer/ 7/ 5
In record 75, Expr1 should read 4 and SumOfstock should read 6, because in record 73, after the initial hammer was checked out, there were 6 left. My plan is that when an item is checked back in, the stock will increase by the number of items added back to stock. I have been unable to achieve this. Additionally, how will I make it so that when something is checked out stock is decremented, but when it is checked baack in, stock is incremented? Any insight will be appreciated.