Stock Balance (1 Viewer)

[ineduinedu]

Greetings everyone, I have been trying to connect quantity sold with stock balance, I have a table of Product, Stockin, and StockOut.... I want a situation where from the various sales of various products(StockOut), I can always get the remaining products in stock, I have tried to use it with this query as shown in the attached database, but when I begin to add stock using the Stockin or remove Stock using StockOut when I run the Query, it brings out an Ambiguous figure.

 

[Gasman]

I tend to do things simply.
Firstly you should only have one table for stock and perhaps have negative values for a stock out transaction.
Then all you do is Sum the records.

With your setup. create a query that sums the products (as you have done now), but multiply the Out by -1.
Then create another query based on that which then subtracts Out from In.

HTH

 

[ineduinedu]

I tend to do things simply.
Firstly you should only have one table for stock and perhaps have negative values for a stock out transaction.
Then all you do is Sum the records.

With your setup. create a query that sums the products (as you have done now), but multiply the Out by -1.
Then create another query based on that which then subtracts Out from In.

HTH

Thank you very much for your response, I am trying to build a Sales DB, I want the Sales rep to have access to just the Transaction form(Stockout) where they will just select the product and enter the quantity sold then immediately they enter the quantity, it will be debited from the Total stock which only the admin will have access to.

 

[Gasman]

That should be no issue?, you can still base the Stock Out form on the same table.?
Personally I would have a combo for Transaction type (Stock Purchase or Sale) and prevent said persons from being able to create a Stock Purchase transaction.

I do something similar for a charity I volunteer for, but the form just records Deposits and Payments and shows outstanding funds for a client by a simple calculation as the Payments are records as negative.

You should NOT be storing stock total, but calculating it when required.

Up to you?

 

[jdraw]

"I want the Sales rep to have access to just the Transaction form(Stockout) where they will just select the product and enter the quantity sold then immediately they enter the quantity, it will be debited from the Total stock which only the admin will have access to. "
Shouldn't you have a check to ensure there is sufficient Stock before the transaction is completed? If the Sales is for quantity 16 and there are only 5 in stock, what should happen in your business?
I agree that current stock total should be calculated based on transactions since the last Stocktaking.

 

[CJ_London]

I agree with the other comments - one table for stock transactions - but be aware there are other transactions that affect stock levels - adjustments due to stocktake/damage/returns etc