Results 1 to 8 of 8

Thread: BOM Structure

  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unhappy Unanswered: BOM Structure

    Hi,

    I'm building am app which contains a requirement to store BOM information. I have an idea how I *could* store the info but what to ask whether this is the optimal way - so any input would be appreciated.

    The BOM's are for unique,custom designed and built parts, and hence do not have assigned part numbers (bar the raw materials), hence I thought of using the order number (for the finished product - since it is unique) as the primary identifier for the BOM, and then add a numbered suffix to the end to reference the next level down to reference the component parts, and so on down the levels...

    E.g. if order no is 234, a sample structure might be:

    234 Finished Part
    234-01 Level 1
    234-01-01 Level 2
    234-01-02 Level 2
    234-02 Level 1
    234-02-01 Level 2
    234-02-02 Level 2
    234-02-02-01 Level 3
    234-02-02-02 Level 3
    234-03 Level 1

    If you catch my drift.

    Then any parts which do have specific part numbers (i.e. the purchsed items/raw materials) could be added in an optional identifier field within the data tables.

    The idea then would be to store each product level in a seperate table (they could all be stored in the same table, but I prefer the idea of separating them). Then if a union query was performed, and a select where ID=234*, and sort on ID, the result would be an accurate BOM. Then a bit of VBA jiggery pokkery would allow indentation of lower levels to generate an easy to understand diagram.

    I spent a very long time mulling over this, and this was the best solution I could come up with, however I am still concerned that there is probably a better solution. If anyone knows a better solution I would be interested to hear it.

    Thans in advance kdm3.

  2. #2
    Join Date
    Jul 2004
    Posts
    34

    response

    I indented the sample BOM, but it seemed to lose its formatting when posted, but just imagine the levels are indented 1 step further each level one goes down.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is there a set number of possible levels?

    Important question...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Jul 2004
    Posts
    34

    response

    I indented the sample BOM, but it seemed to lose its formatting when posted, but just imagine the levels are indented 1 step further each level one goes down.

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yes, I understand that.

    I'm asking if it's possible that you may need more then one set number of levels, in this case 3?

    If there is a set number of levels, then y our table structure is easier to deal with.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Jul 2004
    Posts
    34

    number of levels

    Thankfully, the number of levels is only small, with at most 2/3 (at present) beneath finished item - hence the reason I feel it possible to treat each level independently - if there were many more it would require general code to apply to limitless levels - but that is a task I fear greatly!

    What are your thoughts on this?

  7. #7
    Join Date
    Jul 2004
    Posts
    34
    There isn't a set number of levels, the specific structure depends on the given case. What I think you're asking (though I'm probably wrong) is whether all parts go down to the same level - which alas they don't. And, as you have probably gathered, the location and number of branches varies - basically any structure is possible, but (so far) no products have branched down more than 3 levels. But within that limit there are countless variations, meaning that a standard/proforma isn't really feasible.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    odd... I seem to have double posted...

    Anywho, you could use a table for each level in this scenario, assuming there are a MAX of three tables.

    Then you could use a table for each level and link them by part number.

    Another thought would be a a table for the part, and then another table for partDetail. In the partDetail table, you would use part_id as a foriegn key. You could also define two more fields, one for level, and one for part number. I don't uynderstand what the second grouping of numbers means, but you should be able to adapt this structure to account for that So for instance, given your part number of 234:

    Part
    --------
    part_id (PK)

    partDetail
    ---------
    part_detail_id (PK)
    part_id (FK)
    level
    rank


    so 234 is the key you're pulling form part:

    Code:
    part_detail_id  |  part_id  |  level  |  rank
            1               234           1          1
             2              234           2          1
             3              234           2          2
             4              234           3          1
             5              234           3          2
             6              234           3          3
    etc etc
    again, I'm not exactly sure what that second grouping of numbers means, but you should be able to adapt this with minimal headache.
    Last edited by Teddy; 08-03-04 at 14:52.

Posting Permissions

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