none
SQL - Finding a parent by an exact match of children RRS feed

  • Question

  • I have a situation where I have a list of components (children) that comprise a bill of material for an end item (parent). Since bills of materials are unique to each end item what I want to do is be able to use that list of children to find what end item they belong to. The query needs to match the exact children (no more, no less) back to a single parent. My table structure is like this.

    Finished Assembly Item
    ---------------------------
    ID
    part_number

    Assembled Component
    --------------------------
    ID
    finished_assembled_item_id
    component_id

    Component
    -------------
    ID
    component_number

    What I have at the beginning of the query is several component numbers. It gets tricky because components are often used in several different assembled items, but never in the same combination. That's why I have to match only the exact finished assembled item that matches the component list perfectly. Any direction on this would be greatly appreciated.

    Rich

    Wednesday, November 15, 2017 3:52 PM

All replies

  • Add a 'Pick' field to your component table. Use a form to pull up the list of components and pick the components.

    Run query using the 'Pick' field as criteria.


    Build a little, test a little

    Wednesday, November 15, 2017 5:58 PM