Need help with inventory features

Posted on 2006-04-26
Last Modified: 2008-02-01
How many item numbers should a POS type system support?  Does anyone have suggestions or opinions on a solid design to support the various possible item numbers, SKU's and UPC codes?  This customer app will be more used in construction type stores and will not involve a lot of barcode scanning or heavy customer flow like walmart.

I'm thinking about the following issues:
(A) An Item can sometimes be ordered from several different vendors, and each vendor may or may not use the same item number
(B) Items can contain other items.  Kits may be necessary.
(C) UPC barcodes need to be able to be attached to each item.  Is this the same as the item number? Always, sometimes, never?
(D) Which number do you recommend using for the primary key?
(E) In the invoicing and purchase order screens, an item should be able to be searched for by any of these possible numbers.  But what number what be considered the selling number, or the normal lookup number?  When using the barcode scanner, how do you make a scanner read a upc number, then translate that to the correct "item number", assuming the two are different?
(F) Do you store qty on hand, qty on order, qty backordered, etc. in the same table?
Question by:HKComputer
    LVL 19

    Assisted Solution

    for part number 50 character fiild will be OK.UPS code should be different filed.Vendor part number will be different

    1.table  Inventory - tblInventory
    spartNumber -text(50) -primary key
    sDescription -text (50)
    sUPSCode -text
    curPrice -currency
    curCost - currency
    qtyInstock - number
    qtyAllocated -if you are going to use allocation

    I will create table to keep other vendors part name as well
    tblInventoryVendors(One to many to Inventory)

    Think about warehouses if it will be more then one You will need Warehouse table.

    You will need Inventory stock table to keep track in and out.

    You will need inventory adjustment table as well and if more then two warehouses inventory transfer.

    For kits i would have two tables(header and details,actualy for invoices,POS,PO and ... as well)

    LVL 19

    Expert Comment

    one more for
    Inventory table will be imporpont to have field category adn if you coing to pass it to accounting it should be sales account and cogs account
    LVL 38

    Accepted Solution

    see this tip for your question #F

    LVL 38

    Expert Comment

    ps: the tip includes source code for implementation
    LVL 4

    Author Comment

    I checked out the inventory system by allenbrowne.  I can't decide which way is more complicated.  But because of my skills, I did choose to use dmitryz6's recommended method of inventory tracking.  In my system, the only way to change inventory stock levels is through business documents:

    Inventory Transfer (Transfer between Bins, warehouse locations etc.)
    Inventory Adjust (Adjust for missing, theft, etc.)
    Purchase Orders (To Bring Parts In)
    Expense Entry (An entry towards an asset or COGS item that increases that items value and decreases stock levels)
    Repair Orders (A type of invoice)

    I do have to code to loop through these and take appropriate quantities out.  Also, I haven't implemented a solution yet on how to make changes to an invoice or work order without messing up stock levels.  I think nico would recommend a temporary table to hold the values on entry.  I will probably try that method.  I don't understand how to compare the records  in the temp table to the records at the time the user decides to exit the screen.

    The only thing I don't understand about AllenBrowne's method is that when you do a "stocktake" and the number is different than what was showing, your inventory levels decrease or increase but you never make a document to say where that inventory did go/could have went.  I think my method may be a little more solid in that respect but perhaps not as robust and requires more code.

    I did stumble a little on the how to use a seperate table to hold vendor part numbers.  The theory is fine for reference but when you try to use those numbers as "order as numbers" it can really make things complicated.  A vendor part number needs to be marked as default and only one default is allowed.  And at least one is required.  I've chosen instead  to use these numbers:

    ItemID (Autonumber)
    ItemNo (Your part number, recommended to use mfg's part number)
    UPCNo (It's obvious but this can just include the ItemNo again if no UPC is used)
    PackNo (This allows for a corresponding pack number)
    OrderAsNo1 (Order this part number as this number from the default vendor)
    OrderAsNo2 (Order this part number as this number from vendor number two.)

    An ItemNo is Required
    ItemNo can be duplicate
    ItemNo cannot be changed after it is saved
    A Brand/Mfg is required for each ItemNo (Duplicated ItemNo/Brand records are not allowed)

    The only hiccup I am running into is figuring out how to import price updates.  It wouldn't be uncommon to get pricing information for a specific brand or vendor and then all items in inventory need to have their cost updated.  Now, do you change the cost "where varItemNo = OrderAsNo1" or "where varItemNo = ItemNo and varBrand = Brand" or "Where varItemNo = ItemNo and varVendor = Vendor"?  Seems this could really get complicated.

    So if anyone has more input on the matter, I wouldn't mind hearing it. -HK
    LVL 38

    Expert Comment

    My comments:
    1. Allen Brownes statement about invenory on hand is absolutely correct. His statement is telling you the same thing every database administrator/programmer (including Dmitry) will tell you.....inventory on hand is a derived(calculated) figure that can be computed and displayed on forms and reports as needed. It is redundant to store the results of such calculation in a table.

    2.<<<The only thing I don't understand about AllenBrowne's method is that when you do a "stocktake" and the number is different than what was showing, your inventory levels decrease or increase but you never make a document to say where that inventory did go/could have went>>>>

    You are misinterpreting Mr. Browne.  As is the normal practice, adjusments due to inventory counts are documented as an inventory adjustment and stored in an inventory adjustments table like the one Dmitry mentioned.  

    3. As to pricing(valuing) the quantity on hand.......again as related to the quantity sold or on hand, are generally derived figures computed pursuant to one of the accepted methodologies: LIFO, FIFO. If a Specific Identification is adopted instead of LIFO or FIFO, then the requirements are different.  As related to the Quantity In (purchases) , pricing is specific to each item purchased.
    LVL 4

    Author Comment

    I do appreciate the feedback.  I'm busy thinking...

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now