Stock Calculation (1 Viewer)

IQRARAO

New member
Local time
Today, 08:02
Joined
Jul 11, 2021
Messages
1
I ran a gas plant and working on its table
I have two tables
Sale(SID, Quantity, Amount, VehicleNo, Date)
Purchase(PID, Quantity, Amount, VehicleNo, Date)

I want that at every transaction stock is updated. Can you tell Am I need another table stock which update when ever these table update(If yes then how )

OR

I need to add attribute Stock in Sale that will add when ever purchase transaction is done and substract whenever sale transaction is done.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Sep 12, 2006
Messages
15,614
I ran a gas plant and working on its table
I have two tables
Sale(SID, Quantity, Amount, VehicleNo, Date)
Purchase(PID, Quantity, Amount, VehicleNo, Date)

I want that at every transaction stock is updated. Can you tell Am I need another table stock which update when ever these table update(If yes then how )

OR

I need to add attribute Stock in Sale that will add when ever purchase transaction is done and substract whenever sale transaction is done.

But ideally.

a) I would have a single movements table with purchases recorded as positive, and sales as negative.
b) you actually don't want to keep a running total of the current stock. Just sum the records in the movements table to evaluate the stock.
 

MarkK

bit cruncher
Local time
Today, 08:02
Joined
Mar 17, 2004
Messages
8,178
'Stock' is like 'Age,' it is a moving target that changes with the passage of time. Age is underpinned by 'DateOfBirth,' and for any day you want, you can calculate age. Stock, similarly, is underpinned by purchases and sales, and for any day you want, you should be able to calculate stock, so never store it anywhere, always write a procedure that calculates it for any point in time.
Code:
Function GetStock(OnDate As Date) As Single
'    sum all positive purchases and negative sales in a single tOrder table up to and including 'OnDate'
    GetStock = DSum("Quantity", "tOrder", "[Date] <= #" & OnDate & "#")
End Function
See how that elegantly that calculates your stock level? And note how if you've entered orders that will happen tomorrow and into next week, you can even calculate stock for points of time in the future.
hth
 

Cotswold

Active member
Local time
Today, 15:02
Joined
Dec 31, 2020
Messages
521
You will need a stock table IQRARAO.
The stock level will increase with purchases and reduce with sales. With a Stock table you can then
carry the set prices for stock items in sales. Clearly you may also have rates in the Customer table which
would no doubt ignore the standard stock rate. If there are thousands of stock items then you'll need
associated price lists to use when rates change. The Stock table will be the main table with Codes and
Descriptions. The Sales and Purchases will contain only the code and read the Descr from Stock. You
can't allow Stock and Purchases to have their own descriptions and codes.
Stock levels are validated at the Year End stock take, or when required. The initial stock level will be entered
into the Stock File as a take-on balance. After that Sales and Purchases will make adjustments necessary.
 

Users who are viewing this thread

Top Bottom