[AccessD] FW: Find a record in table with intervals

Chester,

    Sample query given below should get you the latest prevailing value on a given date. 

    T_PriceIndex is the master table showing the price on different dates, while T_PriceCurrent is the table having dates for which the latest prevailing price is required.

    For each date in table T_PriceCurrent, calculated field named PriceCurrent in the sample query gives the latest prevailing price (as collected from T_PriceIndex).

Best wishes,
A.D.Tejpal
--------------

===================================
SELECT T_PriceCurrent.SDate, (Select Price From T_PriceIndex As T1 Where T1.SDate = (Select Max(SDate) From T_PriceIndex As T2 Where T2.SDate <= T_PriceCurrent.SDate)) AS PriceCurrent 
FROM T_PriceCurrent;
===================================

  ----- Original Message ----- 
  From: Kaup, Chester 
  To: Access Developers discussion and problem solving 
  Sent: Monday, July 25, 2005 20:19
  Subject: [AccessD] FW: Find a record in table with intervals


  I have table (table1) with dates and a value associated with the date.

  For example 

  1/1/2001                  20

  7/13/2003                27

  12/26/2003              31

  6/4/2004                  33

  1/13/2005                40

  6/7/2005                  44 

  I have another table (table 2) with values in it also. Using the date in this table I need to find the value in table one that would be correct for that date. It is assumed the value in table one carries forward until the date changes. The value associated with the last date carries forward forever. 

  For example a date of 10/13/2004 should return a value of 33. I was able to achieve the desired result with several if then else and do loops but was hoping for a better solution. Was hoping for something like the vlookup function in Excel. 

  Thanks. 

  Chester Kaup
  Engineering Technician
  Kinder Morgan CO2 Company, LLP
  Office (432) 688-3797