Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I have products that are made from many sub assemblies. I would like to get a total for each sub assembly but my problem is that some of the sub assemblies are made from other sub assemblies and I do not know how to get those totals into the higher level sub assembly.
    If you look at the report for example, sub assembly 1002 has sub assemblies 1004 and 1003, labor operation M40848 and material 001251 that would be included in the cost of sub assembly 1002. Is there a way to get the cost of sub assemblies 1004 and 1003 into the total cost of sub assembly 1002?
    Thanks for looking.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There are no relationships between the tables (and several tables don't have a primary key). This makes it hard to find out how the data fit together.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='781268' date='23-Jun-2009 12:44']There are no relationships between the tables (and several tables don't have a primary key). This makes it hard to find out how the data fit together.[/quote]
    I added a relationship table and added a primary key to the tables where it was needed. If it still does not make sense how everything fits together, let me know and I will try to describe what is happening verbally. Thanks for having a look at it to see if it is even possible.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This looks like a recursive relationship: WIPN 1007 (in tblWIPBillMat) involves 1000, which involves 1001, which involves 1002, which involves 1003 and 1004; 1003 involves 1006 and 1004 involves 1005. So there are at least 5 levels. Databases can easily store such relationships, but there is no built-in support for handling them, so it can get hairy.

    Your relationships still confuse me; for most of them it's not possible to enforce referential integrity, which means there could be ambiguities and conflicts. Could you explain the role played by JN, for example? It appears to signify several things - not a good idea in a database - but perhaps I misunderstand.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='781315' date='23-Jun-2009 16:21']This looks like a recursive relationship: WIPN 1007 (in tblWIPBillMat) involves 1000, which involves 1001, which involves 1002, which involves 1003 and 1004; 1003 involves 1006 and 1004 involves 1005. So there are at least 5 levels. Databases can easily store such relationships, but there is no built-in support for handling them, so it can get hairy.

    Your relationships still confuse me; for most of them it's not possible to enforce referential integrity, which means there could be ambiguities and conflicts. Could you explain the role played by JN, for example? It appears to signify several things - not a good idea in a database - but perhaps I misunderstand.[/quote]
    JN is a number that corresponds to purchased items, labor jobs, or is tied to WIPN for sub-assemblies.
    tblCostBook has what sub assemblies go to make the completed product. tblWIPbillmat has what purchased items, labor jobs, and sub assemblies it takes to make a sub assembly. So JN tells what numbers go to make a complete product or sub assembly whether it be purchased, labor or another sub assembly. If I need to use different names for JN, that can be done.

    I think I see what you are saying about enforcing referential integrity. In tblWIPbillmat, JN is tied to tblInventory and tblJNStdRate so it is not possible to make sure the value for JN is valid in tblWIPBillmat. I am guessing that I need one field in tblWIPbillmat for purchased parts numbers and one field for labor numbers so I can enforce referential integrity.

    I think you understand how the different sub assemblies involve each other.

    Thank you for your time and effort.
    Best Regards,
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='imincorrigible' post='781329' date='23-Jun-2009 23:11']I am guessing that I need one field in tblWIPbillmat for purchased parts numbers and one field for labor numbers so I can enforce referential integrity.[/quote]
    Yes, I think you should use different fields for different purposes: don't store sub assembly numbers, purchased items and labor jobs in the samed field.

Posting Permissions

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