Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: Dealing with variable length products

    Hello,

    I am building an Access database for a small company that builds patio covers and then ships them out to customers.
    I am running into a structural problem. Take this small example:

    Each patio cover consists of a set of louvers. Depending on the specifications given to our company by the customers, each patio cover may have a different sized louvers. For instance, one order may call for a patio cover with 40 louvers that are 18ft in length. Another order could call for 52 louvers at 20ft in length.

    These louvers are ordered by our company in standard 150ft sizes, which must be cut to meet customer requests.

    Now, I am looking to set up this database in the most efficient manner that would be able to calculate INVENTORY. This needs to include inventory of any scrap pieces cut from the original 150ft louver (so that they can be used again on another job).

    Structurally speaking, below is what I have so far in terms of tables. The louvers will be stored in the item table. Where should I store the louver sizes? I am thinking I shouldn't put them in the item table because then there would have to be 150+ entries for louvers alone.

    Item
    ItemID

    Product_Item
    ItemID
    ProductID
    Qty

    Product
    ProductName
    ProductDescription

    Order_Line
    ID
    ProductID
    ItemID
    OrderNum
    Qty

    Order
    OrderID

    Inventory_Transaction
    ItemID
    TransactionType

  2. #2
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    Assuming that an "Item" as you defined it, is a batch of purchased qty of a specific product, and there is some kind of "cutting" operation that changes one of the properties (i.e. the length), I would created an inventory table ItemID, ItemLength, qty.
    Then this "cutting" transaction would allocate qty's of items to orders and generate "scrap" records with different lengths.
    For example assuming you have an order of 40 louvers 18ft lentgh, and that you start from a "clean" inventory of 10 items of 150ft, you would allocate 5 louvers to that order and remain with 5 louvers of 6 ft (I would assume there are more intelligent ways of solving this).
    Am I getting close at all?

  3. #3
    Join Date
    May 2010
    Posts
    23
    The way you described the "cutting" process was correct.

    However, an "Item" is not a batch of products. Rather, the "Product" is a batch of items.

    I am trying to find the best way for the system to handle the cutting process. This will include letting the user choose which piece of scrap should be cut.

Posting Permissions

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