# Thread: is there a easier away of tracing X to X realationships?

1. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820

## Unanswered: is there a easier away of tracing X to X realationships?

basicly have a product and that product is made of other products so i have a 1 to many non obligitory relationship to it's self
for example a DVD product is requires plastic, colour, film, pallet, srink wrap, a carton,... but the plastic is also made of multiple products and on

now to trace it i've had to use

SELECT BoM.PPart, IIf(IsNull(bom_1.ppart),bom.CPART,IIf(IsNull(bom_2 .ppart),bom_1.CPART,bom_2.cpart)) AS [Comp], bom.qtypa*IIf(IsNull(bom_1.qtypa),1,bom_1.qtypa)*I If(IsNull(bom_2.qtypa),1,bom_2.qtypa) AS Weight, IIf(IsNull(bom_1.ppart),bom.enguom,IIf(IsNull(bom_ 2.ppart),bom_1.enguom,bom_2.enguom)) AS Measure, IIf(IsNull(bom_1.ppart),bom.cdes,IIf(IsNull(bom_2. ppart),bom_1.cdes,bom_2.cdes)) AS CDES
FROM BoM LEFT JOIN (BoM AS BoM_1 LEFT JOIN BoM AS BoM_2 ON BoM_1.CPart = BoM_2.PPart) ON BoM.CPart = BoM_1.PPart
WHERE (((BoM.PPart) Like "P*"))
ORDER BY BoM.PPart;

now i can't change the the basic structure but is there a better method ATM it only goes 3 deep but that could change

2. Registered User
Join Date
Nov 2002
Location
San Francisco
Posts
251
you can try TREE solution and extend it - we discussed easier version here http://dbforums.com/t679484.html

simply say, there is just ONE table with products and ONE table with links

TABLE1:
ProductID (unique)
ProduductName

TABLE2:
LinkID (unique) - this is dummy
ProductID
ProductIDUsed

I have sample of CAR, WHEEL, SCREEW (car is made from wheels and screews, wheel is made from screews) - silly sample I know.

the other way is to break First Database Norm and have columns for "made from", but you can get into real problems very very soon.

jiri

3. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
i can't do that as i can't chance the structure (it's an odbc table) the table i've got is
ID, PPart, CPart, CDes, QTYPA, ENGUOM

id is a unique id, ppart is the product code, cpart the product it's made from, QTYPA the quantity of Cpart used and enguom is the unit of measurement KG or EA the closest i could come is duplicating the data... hang on i just want to try something
Last edited by m.timoney; 02-28-03 at 06:40.

4. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
no it's excel the lets you chose which rows to import so the only other thing i can do is dupicate the 5000 rows of data in another table not somthing i want to do the DB already running at 20Mb post compact

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•