Heloo Guys..
I am working on a Inventory project for catering projects. The products are flour, rice, juice, Meat, Fruits, Vegetables, etc. Here the products are consumed/issued.
We purchase items at difference price at different times.
For example :
Juice Bottle bought @ $10 of 200 units.(On Jan 1st 2012)
Juice Bottle bought @ $12 of 500 units.(On Jan 10th 2012)
Juice Bottle bought @ $15 of 300 units.(On Jan 20th 2012)

I want to use FIFO inventory valuation method on this. I can’t use any other methods like Weighted Avg.
Using FIFO : At this point the consumption of 200 units should be of value $10, again if 100 units are consumed then the consumed value should be $12,etc.
How to build database tables on this approach and how to link the tables?
I am using SQL Server 2005 with VB6. At the end of month, I need to display Weekly/Monthly Consumption reports.

I have made following tables so far :
Code:
tblProductsMaster :
Product Code(PK), Product Desc, Qty, UOM(FK), Min Stock Lvl, Reorder Lvl, Category, Price.
Every product is unique record in tblProductsmaster.
Code:
tblUOM :
UOMcode(PK),UOMName.
Code:
tblConsumption :
ConsumptionNo, RequestedBy, RequestedDate, ApprovedBy, ApprovedDate > Header Fields
Product(FK), Product Desc, UOM, Qty, Price > Line Items
Products are entered in Products master having different prices. I don’t need purchase tables, since I directly take the products in tblProductsMaster.
I also need to implement Base UOM, Alternate UOM, Conversion Factor. This is secondary. How to make tables and link them? I don’t know much about creating database tables and linking. Please someone help me……………. It would be very grateful if you help on this matter.