query structure

[Guest]

hi all, i'm trying to figure out the best way the get the results i need for
a report. i have 2 tables. an inventory table with all parts, descr, and
whether or not it's purchased--and a "made from" table. it has 3 fields
ID(pk), part, and made from. there is an entry for each part that makes up
the main part--at most, 8 entries (i think that the table is normalized). my
first idea was to self join with 7 copies-- there at most 7 levels of
assembly. the query produces the right parts but not in a way that i could
easily put in a report.

thank you
--

 

[Guest]

I am guessing at your table structure to be like --
main part part1 part2 part3 .... part7
X-121 AB11 CD34 FR4 TR7

If my assumption is correct then you need to change your database to be --
PartID main partID PartNum
1 X-121 AB11
2 X-121 CD34
3 X-121 FR4
4 X-121 X
5 X-121 Y
6 X-121 Z
7 X-121 TR7
8 X-241 AB57
9 X-241 GH9

You then will have a one-to-many relation between main part and parts table.