recursive query

[SirPoonga]

I am getting a Bill of Materials table from a mainframe and dumping it
into an access table. I am dealing with two fields, ITEM_PARENT and
ITEM_COMP.

Lets say I have item 52555 It may consistion of parts 123, 523, and
432. Those values will show up in the ITEM_COMP field if I query for
the 52555 parent. However, any one of those items may be made of other
parts. So there may be a 123 in the parent field and parts that make
up that.

So to find all the parts that are needed to make 52555, how would I do
that? I'd need a recursive query.

 

[Douglas J. Steele]

See whether http://www.mvps.org/access/queries/qry0023.htm at "The Access
Web" helps.

 

[SirPoonga]

Well, that does what I need. Whew, now comes the hard part.

We are taking data from the old mainframe and moving to the new. We
are only taking parts that have been sold/used in the last 3 years.
Now I have to join that with the sales history table.

This is going to be a challenge

 

[SirPoonga]

What's lft anf rgt fields?
"nested set left value. Note: the index don't inforce NO DUP"
"nested set right value. Note: the index don't inforce NO DUP"

What does that mean? What do those values represent?

 

[SirPoonga]

I thought I replied to this already.
Do you know what the lft and rgt field are for? They appear to dictate
where in the tree that record is. Which defeats the purpose of trying
to find a complete list of BOM if all you are given if the parent part,
componant parts, and quantity.

 

[SirPoonga]

I think I will have to write a recursive VBA function to do this.
After researching it seems DB2 has built in recursion. SQL Server 2005
Will. Didn't read up on oracle yet. Access does not have the
capability unless someone can explain how the lft and rgt field are
derived of that database that Mr. Steele linked. To me it seems like
they are telling you where in the tree the part is. That defeats the
purpose. Plus that setup assumes unique parts for each leave. We have
produsts that use the same steel part so that would show up in several
spots in the tree.