Stock Management

[Uday2009]

I am trying to create a stock management system. I have created a table with the following fields

ProductID, Date,Product name, opening stock, Production, desptches, closing stock.
Now my problem is how do I make the closing stock of the previous day or record be automatically transfered as the opening stock of the current day or record

[vb5prgrmr]

WELLLL..... If this is a transaction table, and you have an autonumber for a unique primary key, then...

There are several ways in which to solve your problem, the following in only one way...

Based upon the first sentance in the hopes that you have a unique primary key you could, as you record each transaction, select top one based upon product id ordered by unique key desc. This will retrun the last recorded transaction and this will tell you the ending balance/quantity of the product in question. Then with that information you can add a new record with your current transaction information with the addition/subtraction to the inventory.

Well, I hope you understood that :)

Good Luck

[Uday2009]

Thanks for posting

But can you please tell me this with an example pls.

[vb5prgrmr]

Well your query string would be something like...

SQLString = "Select Top 1 ClosingStock From YourTable Order By UniqueAutoNumberID DESC"

Then you execute it via your data access method (RDO/DAO/ADO) and retrieve the value (check first if you have actually retrieved a record though and if not then forwarding balance (closingstock) would equal zero).

Good Luck

[Uday2009]

This is about Inventory.

The question is that there are total 7 tables.
Sr. No. Field Name Description
1) Product_Id is primary key
2) ProductName Name of Product
3) MfgDate Mfg. Date of Product
4) OpeningStock Opening Stock of Product
5) Production Production on Mfg. Date
6) Despatches Transfered to Godown
7) ClosingStock Stock at the end of day

When i or any user feed the stock of the next day. Then he automatically must get or receive the closing stock of previous day as opening stock of that day.