WOW. Thats alot of text :-)

A long story made short. I have not been able to do BOM's via SQL properly. I know that sucks but that is my experience.

I am in manufacturing my self so this is SOP stuff for me.

You need more tables than this to do what you want.
BOM - holds the listing of all parent, child parts . This is your make file I believe.

For me I have a configurator that helps the OE people pick the proper parts, or Sub Bills, that are available on our doors. You can choose between 5 locks on some and 3 on others.

So in the end I will get about 10 parts, or sub bills that when combined make our door. This is my configuration file.

When I get the final output from the configuration file and fetch all the parts for a cost, I have to build a working table and scan through it inserting into all sub bills, and parts.

I have some bills that go through 4 levels. I have never gotten this to work in SQL. When I get some free time I play, but I don't have the patientce that I see in Software Developer or DBMS magazines.

So what I do in psuedo code:
sele configuration file {view with parameters vp_sono vp_lineno} all fields to my working table.

Set a relationship to my inventory file

Scan through the working file to see if the part is a BOM or a part.
If a BOM, SELE all parts that make up the sub bill and insert them into the working table.
Keep going through the working table untill everything is a PART.
endscan

Your done.





>Thanks for your help, but it didn't work.
>
>Let me explain what I am trying to do. You guessed right, the
>parts and parts_a table are the same and used again. In
>english, parts is a list of parts, which may or may not contain
>other parts (say some parts might be drawings). The make table
>is a table that defines what are the "sub-parts" of a part.
>The sub-parts are of course regular parts also defined in
>the parts table, and in order to get a descriptive name for
>those parts, they need to be looked up in the parts table,
>and hence the reason to use it AGAIN.
>
>So, I want a query that will show all the parts with their
>subparts (if any). The make table stores parts IDs only.
>
>I want my query to look like this:
>
>part name sub part name
>An a1n
>An a2n
>Bn .NULL.
>Cn c1n
>
>An n indicates that I want to show part number (only in the
>parts table), not part ID which is the short code I use
>in the make table.
>
>The parts table contains part ID and part number:
>
>part ID part number
>A An
>B Bn
>C Cn
>a1 a1n
>a2 a2n
>c1 c1n
>
>The make table would look like this for this example:
>
>part sub-part
>A a1
>A a2
>C c1
>
>Where part A has 2 sub-parts (a1, and a2), part B does not
>have any sub-parts (but it is reported), and part C has
>only one sub-part.
>
>So, what I thought I needed to do is do a left join between
>parts and make (to make sure the parts that don't have sub-parts
>get reported), and then to get a description for the part IDs
>through an inner join with the parts table AGAIN.
>
>Thanks for your help.
>
>
>>YOu have to go back to the basics here:
>>
>>Outer Joins will get UNMATCHED data and combine it with .null.
>>Inner Joins will get data that match from BOTH tables.
>>
>>I am gurssing that parts and Parts_a are the same table used AGAIN?
>>
>>So :
>>>SELECT Parts.whatever, make.whatever, parts_a.whatever;
>>> FROM data1!parts LEFT OUTER JOIN data1!make;
>>> ON Parts.p_part_id = Make.m_part_id;
>>> INNER JOIN data1!parts Parts_a ;
>>> ON Make.m_sub_part = Parts_a.p_part_id ;
>>> ORDER BY Parts.p_part_number;
>>> INTO TABLE parts_c.dbf
>>
>>
>>The key to the above is OUTER "Parts to Make," and INNER "Parts Parts_a"
>>but your "ON" says Make.m_sub_part = Parts_a.p_part_id ;
>>
>>This is the problem with Query Designer. Drop it and get to the command window to get it straight.
>>
>>I think that you are trying to find all alternate parts in part_a for make when orig. part in part is out of stock or in question?
>>
>>SELECT Parts.whatever, make.whatever, parts_a.whatever;
>> FROM data1!parts LEFT OUTER JOIN data1!make;
>> ON Parts.p_part_id = Make.m_part_id;
>> INNER JOIN data1!make Parts_a ;
>> ON Make.m_sub_part = Parts_a.p_part_id ;
>> ORDER BY Parts.p_part_number;
>> INTO TABLE parts_c.dbf
>>
>>I think that this should work.
>>
>>__Stephen Russell
>>Memphis VFP User Group
>>
>>
>>>I have a 3 table query, which I originally generated with the
>>>VFP Query builder. The original VFP query looks like this:
>>>
>>>SELECT parts.whatever, ..., make.whatever , parts_a.whatever;
>>> FROM data1!parts LEFT OUTER JOIN data1!make;
>>> INNER JOIN data1!parts Parts_a ;
>>> ON Make.m_sub_part = Parts_a.p_part_id ;
>>> ON Parts.p_part_id = Make.m_part_id;
>>> ORDER BY Parts.p_part_number;
>>> INTO TABLE parts_b.dbf
>>>
>>>I was able to save this query, but since I thought the
>>>INNER JOINs should be next to their respective ONs, and I
>>>thought it was a bug with the VFP query editor, I changed
>>>it to read like this:
>>>
>>>SELECT Parts.whatever, make.whatever, parts_a.whatever;
>>> FROM data1!parts LEFT OUTER JOIN data1!make;
>>> ON Parts.p_part_id = Make.m_part_id;
>>> INNER JOIN data1!parts Parts_a ;
>>> ON Make.m_sub_part = Parts_a.p_part_id ;
>>> ORDER BY Parts.p_part_number;
>>> INTO TABLE parts_c.dbf
>>>
>>>The results I get are very different. I want a LEFT
>>>OUTER JOIN between parts and make, which I was getting
>>>from my first query, but not from my second query. I
>>>am not sure what I am getting from my second query,
>>>but it does not show all the records from parts, which
>>>is what I would expect from a left outer join.
>>>
>>>What am I doing wrong?