table a
id pct
1 0.1
2 0.24
3 0.5

table b
year amount
2001 10
2002 20
2003 30
2004 23
2005 67
2006 80

I want to create a table ret
wiht this
year newamount
2006 80*0.1+67*0.24+23*0.25
2005 67*0.1+27*0.24+30*0.25
2004 23*0.1+30*0.24+20*0.25
2003 30*0.1+20*0.24+10*0.25=9.9

How can i do that.
Thanks.
Jeff

You asked for a table, but here is a query:

SELECT Table_B.Year, Max([Table_B]![Amount]*IIf([Table_A]![ID]=1,[Table_A]![Pct],Null)) AS Amount_1, Max([Table_B]![Amount]*IIf([Table_A]![ID]=2,[Table_A]![Pct],Null)) AS Amount_2, Max([Table_B]![Amount]*IIf([Table_A]![ID]=3,[Table_A]![Pct],Null)) AS Amount_3, [Amount_1]+[Amount_2]+[Amount_3] AS Amount_Total
FROM Table_A, Table_B
GROUP BY Table_B.Year
HAVING (((Max([Table_B]![Amount]*IIf([Table_A]![ID]=1,[Table_A]![Pct],Null))) Is Not Null)) OR (((Max([Table_B]![Amount]*IIf([Table_A]![ID]=2,[Table_A]![Pct],Null))) Is Not Null)) OR (((Max([Table_B]![Amount]*IIf([Table_A]![ID]=3,[Table_A]![Pct],Null))) Is Not Null));


Your initial data seems to indicate some typos, so I have assumed so and corrected them based on assumptions. For example the pct shown for ID 3 is .5, yet the multiplier for for the calculated amounts is .25, so for my testing purposes, I assumed the .25 is the correct value for ID 3 in Table A.
Regardless of this, this query runs based on whatever values you have in the fields.

If you actually need this in a table, you can write an append query to append these values to a table.
HOWEVER, this type of query is NOT UPDATABLE so you will not be able to use it to update fileds in existing records.

Thanks

Jeff