Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mutilpy factor in tbl_A to figures in tbl_B (a2k)

    Hello. How are you all.

    I have a products table. Each product has aspects which affect its price. Lets say Volume (M3), Weight (KG) and Labor (hours)
    In another table I have factors which apply to those price affecting aspects of the products. $35/m3, $20/KG and $30/hour

    Lets say in the products tabel we have product one,2m3,100kg,30hours
    How do I create a query that does the math to get...
    product one,$70,$200,$900,$1170

    Thanks very much.

    Jason

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mutilpy factor in tbl_A to figures in tbl_B (a2k)

    It depends on how you have designed the tables. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mutilpy factor in tbl_A to figures in tbl_B (a

    Thank you Hans.

    The mdb is attached (stripped and zipped)

    In factors table I added a field to roughly describe what that factor is to be used for.

    Thanks
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mutilpy factor in tbl_A to figures in tbl_B (a

    I have attached your database with a query that demonstrates a way to approach this. The query is not complete, I only performed a few of the desired calculations.

    The tables named W, L, H etc. are all copies of tbl_price_factors with a different alias. Each is filtered for a different value of factorID.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mutilpy factor in tbl_A to figures in tbl_B (a

    Hans.

    Thanks. Got it working...Great solution help for my mediocre table design.

    Should I have designed my tables another way?
    tbl_products
    tlb_products_price_affecting_aspects
    tbl_products_to_products_affecting_aspects

    Or some better way?

    Thanks again.

    Jason

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mutilpy factor in tbl_A to figures in tbl_B (a

    The way you set up tbl_price_factors is a great way to document it, it helped figuring out how to use it. But for efficiency it would be better to have a table with just one record, with each factor value in a separate field (and hence no self-documenting at all). You would only need one copy of tbl_price_factors in the query and you could use the various factor fields directly.

Posting Permissions

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