How do I perform a date lookup and calculation in ACCESS

I have two tables:- table(A) contains a date and a numerical value which can
change at varying intervals (weeks, month etc.) Table(B) contains "date"
(daily information) and several numeriacl values.

I need to build an expression that will look at the date in table (A) and
use the numerical value to make a calculation from criteria in Table (B). I
would want the expression to continue making the calculation until it sees a
new date and value in table (A) then it would carry on making the calculation
using the new value until there is another change etc.

I think I need to perform a lookup but I am not sure how

Can anyone advise me of the right course of action for this?

Thanks

st0rm wrote:

I have two tables:- table(A) contains a date and a numerical value which can
change at varying intervals (weeks, month etc.) Table(B) contains "date"
(daily information) and several numeriacl values.

I need to build an expression that will look at the date in table (A) and
use the numerical value to make a calculation from criteria in Table (B). I
would want the expression to continue making the calculation until it sees a
new date and value in table (A) then it would carry on making the calculation
using the new value until there is another change etc.


I think this is what yo'r looking for:

SELECT B.*, (SELECT Top 1 A.numval
FROM tableA As A
WHERE A.datefield B.dailydate
ORDER BY A.datefield DESC
) As CurrentPrice
FROM tableB As B

--
Marsh
MVP [MS Access]