# Thread: Mutilpy factor in tbl_A to figures in tbl_B (a2k)

1. ## 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. ## 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. ## 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

4. ## 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.

5. ## 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. ## 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
•