Mutilpy factor in tbl_A to figures in tbl_B (a2k)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote