How do I perform a date lookup and calculation in ACCESS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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.
Click to expand...


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
 
Back
Top