Update Stock Inventory

[Mohammad Asad]

Hi All,

I am in the process of making a database for stock inventory. In the start
of every month, I receive a stock quantity, that needs to be used for the
rest of the month, lets say 2000. Now, after the first day of each month, I
start using the stock. For example: On Aug 1st, I received a stock of 2000
glasses and on August 2nd, I started using stock, by giving it out in the
market everyday.

Can somebody tell me how can I do that, so that in the end of the month, if
I take a printout of a report, it looks something like this.

Date Initial Quantity Used Quantity
Remaining Quantity
08/01/07 2000 200
1800
08/02/07 1800 300
1500
08/03/07 1500 400
1100

I am already aware that there was somehelp provided on this issue in the
previous help questions, but I would appreciate if somebody could specify
some more details on it.

 

[Al Campagna]

Mohammad,
There are many ways to handle an "inventory." In your example, you
appear to have an inventory of only one item. In the real world, an
inventory would be able to track and calculate "on hand" for many items.

I prefer to use just one table that works much like a checkbook (money
in/money out)
Date PartNo DebitCredit
1/1/07 XYZ +2000
1/2/07 XYZ -200
1/6/07 ABC +1000
1/7/07 XYZ -300
1/15/07 XYZ +1000
1/20/07 ABC -500

A report, grouped on PartNo and sorted by date, and with a Running Sum would
yield...
Date PartNo DebitCredit RunSum
1/1/07 XYZ +2000 2000
1/2/07 XYZ -200 1800
1/7/07 XYZ -300 1500
1/15/07 XYZ +1000 2500

and for the ABC PartNo...
Date PartNo DebitCredit RunSum
1/6/07 ABC +1000 1000
1/20/07 ABC -500 500

--
hth
Al Campagna
Microsoft Access MVP
Candia Computer Consulting
http://home.comcast.net/~cccsolutions/index.html

 

[Mohammad Asad]

Your help saved the day! Thank you SO much man! Take care!