FirstInFirstOut Help in Stores Inventory

[shyamsundar]

Hi Techie Guys/Gals,

I am developing Inventory system in MS-Access, I recently switched from foxpro for DOS to MS-Access. I need your help on this. Before asking help, let me describe how i designed the system.

I have 6 tables namely Master Item, Ledger, Receipts,Receipt_item, Issues and Issu Item.

1.Master Item consists of ItemCode,ItemName,OpeningBalance & Present Balance.(I want the present balance is automtically updated (i.e added when recieved, substracted when issued)

2.Ledger consists of ItemCode,Date,Price,OpeningBalance,Present Balance
(The difference between master & ledger is, I can have same item with different prices, obviously on different dates, that is why i included date field here).

3. Receipt consist of Rect_no,Rect_date,Rect_from

4. ReceiptItem - Rect_no(related with receipt table),item_code, price & Quantity

5. Issue consist of ISS_no,ISS_date,ISS_to

6. IssueItem - Iss_no(related with Issue table),item_code, Quantity Issued.

Now what I want is...

While recieving each item, it will look into master table for item code and update(add)quantity. Then look into ledger table for item code and price, if itemcode and price in receipt matches in ledger it will just update (add) quantity to item, else it will add a new row with itemcode,receipt date,price and quantity from rect_item)

And, while issueing, it will look into master table for item code and update(sustract)quantity. Then look into ledger filter for issued item code ordered by date, and issue accordingly. for example the issue quantity is 5 and your ledger rows are

C001 12-12-2002 $55.60 2
C001 12-13-2002 $56.10 4

Then it should take (55.60 x2)from 1st row and (56.10 x 3) from second row.

I hope i am clear. Any help is highly appreciated, suggestion to change the above logic is also highly appreciated.

Thanx in advance



Shyam
cwizshyam@yahoo.com

 

[cbuono]

Hi Shyam,

you may want to take a look at how you are setting up your tables first. Typically an inventory system will contain the following:
1. Product Table - Product Number, description, weight, cube, etc.
2. Inventory Table - Product Number, Qty On Hand, etc.
3. Inventory Transactions - Product Number, TransactionType(Receipt,Order), TransactionNumber (ReceiptNumber, OrderNumber), Qty (either QtyReceived, QtyIssued).
4. ReceiptHeader - Customer, ReceiptNumber, DateReceived.
5. ReceiptDetail - Customer, ReceiptNumber, LineNumber, Product Number, QtyReceived.
(ReceiptHeader and ReceiptDetail will be related by the ReceiptNumber).
6. OrderHeader - same as ReceiptHeader.
7. OrderDetail - same as ReceiptDetail.
(OrderHeader and OrderDetail will be related by order number).

The logic then goes that when you receive a product into the facility you add a transaction to your inventory transaction table and you update the inventory table to the new qty (or you may add a record if it is the first receipt into the facility). When a product is issued again a transaction is created and the inventory is updated accordingly.
This way at anytime you can look at your inventory table and see exactly what you have in your facility. If you need to go back and see what your initial balance was you can create a report that will check the transactions table and calculate accordingly.

Hope this helps. I've actually written an inventory system that I sell. If you or your company is interested please let me know.
Good Luck.

Chris
cbuono@jikkou.com