Results 1 to 4 of 4
  1. #1
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    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
    Attached Files Attached Files

  3. #3
    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.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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