Bill of Material Query & Report

Discussion in 'Microsoft Access Queries' started by Guest, Feb 1, 2005.

  1. Guest

    Guest Guest

    I need to write a query that provides a multi-level Bill of Material using
    Access. The table is structure with the following fields:
    Assembly(parent)
    Component(child)
    ItemSequence
    QtyPer

    I am currently able to write a single level Bill of Material query. I need
    to set it up for about 6 levels deep. I know it can be done using a nested
    query but I haven't figured it out yet. Can anyone help me with this?
    Thanks in advance.
    Samantha
     
    Guest, Feb 1, 2005
    #1
    1. Advertisements

  2. Guest

    Michel Walsh Guest

    Hi,


    If the number of level is fixed, you can try (here, for a max of 3
    levels):


    SELECT d.who, [a].[qtyper]*.[qtyper]*[c].[qtyper]*[d].[qtyper] AS Expr1
    FROM ((ParChi AS a LEFT JOIN ParChi AS b ON a.who = b.parent) LEFT JOIN
    ParChi AS c ON b.who = c.parent) LEFT JOIN ParChi AS d ON c.who = d.parent
    WHERE (((d.who) Is Not Null));



    with


    ParChi
    who parent qtyper
    1 1 1
    2 1 4
    3 1 5
    4 2 3
    5 4 1
    6 2 3




    the result is


    Query10
    who Expr1
    5 12
    3 5
    2 4
    1 1
    6 12
    4 12



    which can be read a we need 1 piece 1, 4 pieces #2, ... 12 pieces #6 ( 3
    for each #2 we make, and need 4 pieces #2)




    For a variable number of nesting levels, it may be preferable to use a
    nested set approach.



    Hoping it may help,
    Vanderghast, Access MVP



    "Samantha" <> wrote in message
    news:...
    >I need to write a query that provides a multi-level Bill of Material using
    > Access. The table is structure with the following fields:
    > Assembly(parent)
    > Component(child)
    > ItemSequence
    > QtyPer
    >
    > I am currently able to write a single level Bill of Material query. I
    > need
    > to set it up for about 6 levels deep. I know it can be done using a
    > nested
    > query but I haven't figured it out yet. Can anyone help me with this?
    > Thanks in advance.
    > Samantha
     
    Michel Walsh, Feb 1, 2005
    #2
    1. Advertisements

  3. Guest

    Guest Guest

    hi,
    i doubt you will do it with 1 query.
    i had to do multi-level Bill of Materials up to 4 levels
    deep. I had to have a query for each level which i dumped
    into a temp table. i had to create a form just for BOMs
    because of all the different queries that had to run which
    included a delete temp table query which i had to run
    before the next BOM could run. i use a select case to test
    the parent item id entered in the form and run the
    appropreate series of queries based on level on the id
    entered.
    post back if you need more info/direction.

    >-----Original Message-----
    >I need to write a query that provides a multi-level Bill

    of Material using
    >Access. The table is structure with the following fields:
    > Assembly(parent)
    > Component(child)
    > ItemSequence
    > QtyPer
    >
    >I am currently able to write a single level Bill of

    Material query. I need
    >to set it up for about 6 levels deep. I know it can be

    done using a nested
    >query but I haven't figured it out yet. Can anyone help

    me with this?
    >Thanks in advance.
    >Samantha
    >.
    >
     
    Guest, Feb 1, 2005
    #3
  4. Guest

    Guest Guest

    Hi,
    I tried the self-joining process, which works for up to 3 levels. When I
    added the 4th level, it's too complex and keeps crashing the database.

    Can you provide me with more detail/info on how you designed yours? And how
    long did it take to run your report?

    thank you so much! I've spent so much time on this already trying to get it
    to work!
    -S
    "" wrote:

    > hi,
    > i doubt you will do it with 1 query.
    > i had to do multi-level Bill of Materials up to 4 levels
    > deep. I had to have a query for each level which i dumped
    > into a temp table. i had to create a form just for BOMs
    > because of all the different queries that had to run which
    > included a delete temp table query which i had to run
    > before the next BOM could run. i use a select case to test
    > the parent item id entered in the form and run the
    > appropreate series of queries based on level on the id
    > entered.
    > post back if you need more info/direction.
    >
    > >-----Original Message-----
    > >I need to write a query that provides a multi-level Bill

    > of Material using
    > >Access. The table is structure with the following fields:
    > > Assembly(parent)
    > > Component(child)
    > > ItemSequence
    > > QtyPer
    > >
    > >I am currently able to write a single level Bill of

    > Material query. I need
    > >to set it up for about 6 levels deep. I know it can be

    > done using a nested
    > >query but I haven't figured it out yet. Can anyone help

    > me with this?
    > >Thanks in advance.
    > >Samantha
    > >.
    > >

    >
     
    Guest, Mar 9, 2005
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike

    Creating a Bill of Materials Query

    Mike, Mar 26, 2004, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    1,481
  2. Guest

    querying a bill of material table

    Guest, May 10, 2005, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    169
    Guest
    May 13, 2005
  3. Nick Bradbury

    Calculating Material Usage

    Nick Bradbury, Nov 10, 2006, in forum: Microsoft Access Queries
    Replies:
    7
    Views:
    256
    Nick Bradbury
    Nov 15, 2006
  4. locochon2010

    bill of materials query

    locochon2010, Dec 26, 2007, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    378
    S.Clark
    Dec 26, 2007
  5. DKS

    Bill of material type query

    DKS, Oct 7, 2009, in forum: Microsoft Access Queries
    Replies:
    3
    Views:
    146
    Phil Smith
    Oct 8, 2009
Loading...

Share This Page