Building a Stock control database?

Building a Stock control database?

hi i am needing to build a stock control database for my work.

the database needs to keep track of all items used by the business (identified using unique numbers) but it also needs to be able to handle batches of those items in a seperate table (here the batch number is unique).

the two need to be linked somehow so that looking up a barcode will bring back a list of all the batches associated with that barcode.

note; i am using openoffice v.3 fully updated etc.
p.s i need help building the relationships between the tables.
i have the fields all laid out and the table constructed however i am having trouble creating the lookup functions between them.
the relationship window says there is one there, but it make no difference to the table whatsoever.

Tags: , , ,

Under Forum

2 Comments for Building a Stock control database?

  • 1. Germann A  |  November 25th, 2010 at 7:36 pm

    tables:

    batches (batchID, …);
    batchDetails (batchID, itemID, …); – will have 1 record per item in each batch
    items (itemID, …);

  • 2. david  |  December 29th, 2010 at 4:07 am

    Your item table, i assumed to be your stock_table, and batches to be your stock_transaction table. If this is your relationship, you are getting close to where you should be. If i can add on, you may need to include some field to capture your stock_type (GRN,DO,ADJ) and costing_method (fifo/lifo) in your tables. The stock_type will help in your reporting query, costing_method is to facilitate your stock movement pricing when you bill and purchase your stock (look for a book from accountingdes, it gives you some ideas on how to start creating your stock control system.)

    Good Luck

Leave a Comment for Building a Stock control database?

You must be logged in to post a comment.

Trackback this post  |  Subscribe to the comments via RSS Feed

Categories