Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Unanswered: Thoghts on inventory Management Module?

    OK, so this is a little complicated, so please bear with me. My company uses a cash accounting method in Quickbooks, but I have a division where we need to keep track of inventory for multiple sites, primarily for keeping track of quantity on hand and being able to research historical transactions. I am thinking that as long as I am building this, I might as well build in a valuation method. So:

    I am thinking of having the following: (I’ll attempt to include images of what I’ve already got [with some artifacts of some stuff I was toying with in the past])
    tblItemMaster (contains information about the SKU)
    tblWarehouseMaster (contains information about Quanitities for the site, and authorized stock levels)
    tblInventoryTransactions (contains historical transaction of SKU Items) - SKU level
    tblInventoryDocuments (will be modified to allow for multiple trans types) – Document level
    tblInventoryFIFOLIFO – contains historical information about items placed in inventory and sold- the intention is to “check off and record when sold” items from inventory. If FIFO, always use the first item first, if LIFO then always use the last item first, if “average cost” then always subtract the first item in inventory. Also if average costing, update the cost of all related SKU’s to update the average when a new item is purchased. This approach allows the accounting method to be changed in the future – although an update might be required from going from one accounting method to another.

    In general, I am thinking that I need to:
    1. Order inventory – so update the “on order” for whichever warehouse
    2. Receive inventory – so update on order, add to inventory for specific warehouse, insert into the FIFOLIFO table, and somehow “check off” the Order document as received.
    3. Transfer Inventory – decrease inventory from one warehouse and add it to another
    4. Sell Inventory – Reduce stock on hand from warehouse and check off/use item from FIFOLIFO table
    5. Adjustments – variations of order or sell to correct inventory discrepancies or return defective product to vendor (and possibly receive defective produce back from customer)
    6. Manage “core returns” basically by creating an order to a returns warehouse, and creating a returns document for clearing inventory from the returns warehouse.

    I had started this, then adjusted it for a much more ambitions project (some of the artifacts of the more ambitious project still show in the existing tables, and now want to abandon the more ambitious in favor of “simple” inventory management. But before I do, I wanted to see if anyone here had thoughts on the approach.

    Please recognize that I know that a true accounting system would be better, but it’s not in the cards with my company at the moment. AND, I need to manage inventory quantities somehow, AND I need to request and list items used on work orders from my existing database anyway, so given all of that, it makes sense to try to do something along these lines.

    Any thoughts on the approach?
    Attached Thumbnails Attached Thumbnails tblInventoryDocuments.JPG   tblInventoryFIFOLIFO.JPG   tblInventoryReceiptDocuments.JPG   tblitemMaster.JPG   tblItemWarehouseMaster.JPG  


  2. #2
    Join Date
    Aug 2006
    Posts
    559
    There are a large number of inventory control databases you can download, Microsofts website has some, and customize to do what you want.

    From the requirments that you stated above (your 6 tasks) it doesn't seem that you should have a very difficult time doing so. I'd only suggest to draw your ideas on paper first before you start doing anything because it makes it much simpler to make sure you're going in the right direction.

  3. #3
    Join Date
    Oct 2003
    Posts
    103
    Quote Originally Posted by Grafixx01
    There are a large number of inventory control databases you can download, Microsofts website has some, and customize to do what you want.
    I'm not sure where you are speaking of, I did a search at support.microsoft.com, but didn't find anything for inventory. Maybe I'm not searching the right site, or maybe there is another keyword I should use?

    Thanks for the reply though.

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by jdostie
    I'm not sure where you are speaking of, I did a search at support.microsoft.com, but didn't find anything for inventory. Maybe I'm not searching the right site, or maybe there is another keyword I should use?

    Thanks for the reply though.

    You have to go to "Office" and then you'll see it. In any event, the link below should work. I even searched for "inventory" for you...


    http://office.microsoft.com/en-us/do...x?qu=inventory

  5. #5
    Join Date
    Oct 2003
    Posts
    103
    Thanks, I've downloaded them, I don't think they are quite what I'm looking for, but you never know, it might give me an idea or two.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    It'll at least give you some thing to visually work off of.

    Like I suggested though, I'd write it all down on paper that way you can get it "working" correctly and know all properties of things you need, make sure your 'PK's and 'FK's are correct and then get it in Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •