Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    68

    Unanswered: getting the prices from table for multipart item

    So i have an inventory Table which consist of (and not to name all fields)

    iid (PK)
    categoryid
    Material
    classid
    perid
    unitprice

    I need to add an item to the inventory which consists of 3 or more items from the inventory table itself. for example:

    i need to add "stuff"

    "Stuff" as a whole part is made from "this, that, and other". "This, that and other" already exist in the table and have unit pricing associated with that record. I want the "stuff" unit price to equal the summed cost of "this, that and other".

    Hope that explains it well. Is this a no-no in access? I have thought trough creating queries, but have hit deadends at every path i take. Any ideas

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need a gozinto table

    eg
    Assembly oojamacallit comprises
    9 of this
    3 or that
    7 of the other
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    68
    Should i create a table for each MultiPart?

    eg
    tblVolume Damper 1/4"

    Fields:
    material
    qty
    unitprice

    tblVolume Damper 3/8"

    Fields:
    material
    qty
    untiprice

    etc. How would i get the calculated price back into tblInventory for "Material" - Volume Damper 1/4"?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    table for each no, violates normalisation rules, is a pain to maintain

    a gozinto table may be
    productID ' fk to productID in products table
    GozintoProduct ' fk to productIDin products table, both are PK in gozinto
    Qty

    as to whether you store the price at each individual product level, thats up to you. there's many who would say don't as its derived data, but there could be good performance reasons to store it (and say regenerate the price every time a constituent part is changed, or even do it over night, or once a week/fortnight whatever floats your boat)

    its been said in the past that there's usually no more than 6 levels in a final assembly
    final assembly
    sub assembly
    component
    part
    material

    any assembly may be made of sub assemblies and parts, and concievably material. in terms of stock all 5 levels are just stock and should be treated as the same (ie a final assembly is a stock item as is a sub assembly as is a component. the fact that one may comprises masses of others is irrelevant,t hey are still stock items.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2012
    Posts
    68
    I made a table called tblMultiPartMaterial.

    Fields are:
    id (PK)
    iid (lookup column) relationship to tblInventory "iid"
    part (lookup column) lookup "iid" in tblInventory
    qty (number)

    Now those parts are linked to that assembly, but how can i show the calculated cost in the tblInventory? I imagine this will need to be done from a query.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you think you need to use a surrogate key ID to provide the primary key?
    especially when there is a perfectly good enough key, a composite of the two parts referred to.

    should you model allo the same part to be linked to a parent part more than once?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2012
    Posts
    68
    Im a newb, but i thought every table needed to have a PK or at least it was good practice to have one. Would this be a better?

    Fields:
    iid (lookup column) relationship to tblInventory "iid"
    part (lookup column) lookup "iid" in tblInventory
    qty (number)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by bsigmon1103 View Post
    Im a newb, but i thought every table needed to have a PK or at least it was good practice to have one. Would this be a better?

    Fields:
    iid (lookup column) relationship to tblInventory "iid"
    part (lookup column) lookup "iid" in tblInventory
    qty (number)
    true
    you ought to have a means of uniquely identifying a row in a relational database

    hence my comment about making the primary key a composite of the two part numbers

    in my way you cannot define the same part relationship, in your way (unless you define an additional unique index you have no way of stopping the same pairing being defined
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2012
    Posts
    68
    Quote Originally Posted by healdem View Post
    hence my comment about making the primary key a composite of the two part numbers
    Not quite sure i understand how to do this.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select both columns in the table designer
    then click the primary key icon

    or you may have to open the indexes dialog
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2012
    Posts
    68
    I am so lost. Thank you so much for helping me. Im spinning wheels. I'm just not getting it.

    So here is the scenario:

    A 1/4" Volume Damper is "iid" 357 from tblInventory

    a 1/4"Volume Damper cosists of
    "iid" 281 - qty of 1
    "iid" 286 - qty of 2
    "iid" 358 - qty of 2

    I created a table called tblMulitpartmaterial
    fields:
    iid
    part
    qty

    I cannot grasp how to do it any other way. I think i might just give up on this one. Seems a bit out of reach for me. I just want the unit pricing to update automatically when any other part price is updated.

Posting Permissions

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