[AccessD] Carry Forward in a query

Chester,

    It is presumed that table T_PriceIndex too has the field named Pattern, with appropriate entries. In such a case, you could try modified SQL as follows:

SELECT T_PriceCurrent.SDate, 
(Select Price from T_PriceIndex as T1 where T1.Pattern = T_PriceCurrent.Pattern And T1.SDate=(Select Max(SDate) 
from T_PriceIndex AS T2 where T2.Pattern = T_PriceCurrent.Pattern And  T2.SDate<=T_PriceCurrent.SDate)) AS PriceCurrent
FROM T_PriceCurrent;

    Caution: Thi is AirCode - The suggested SQL has not been subjected to actual test.

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

  ----- Original Message ----- 
  From: Kaup, Chester 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, November 15, 2006 04:10
  Subject: Re: [AccessD] Carry Forward in a query


  Here is what I have now. One table with dates and one table like below.
  Query SQL as below. This works great.

  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;

  SDate Price
  1/1/2005 8.56
  1/23/2005 8.73
  2/14/2005 8.40
  3/1/2005 8.00
  3/12/2005 8.45
  3/27/2005 8.95
  4/7/2005 8.12
  4/30/2005 7.95
  5/14/2005 8.03
  5/27/2005 8.24
  6/10/2005 9.03
  6/25/2005 9.17
  7/3/2005 9.25
  7/27/2005 9.40
  8/3/2005 9.64

  What I cannot figure how to do is make it work if table looks like
  below. I need output for each Pattern.
  Pattern SDate Price
  1 1/1/2005 8.56
  1 1/23/2005 8.73
  1 2/14/2005 8.40
  1 3/1/2005 8.00
  1 3/12/2005 8.45
  1 3/27/2005 8.95
  1 4/7/2005 8.12
  1 4/30/2005 7.95
  1 5/14/2005 8.03
  1 5/27/2005 8.24
  1 6/10/2005 9.03
  1 6/25/2005 9.17
  1 7/3/2005 9.25
  1 7/27/2005 9.40
  1 8/3/2005 9.64
  2 1/7/2005 8.27
  2 1/24/2005 8.71
  2 2/13/2005 8.35
  2 3/2/2005 8.02
  2 3/13/2005 8.44
  2 3/28/2005 8.93
  2 4/6/2005 8.10
  2 4/28/2005 7.98
  2 5/15/2005 8.05
  2 5/25/2005 8.20
  2 6/9/2005 9.00
  2 6/20/2005 9.15
  2 7/1/2005 9.23
  2 7/25/2005 9.35
  2 8/4/2005 9.65
  2 8/15/2005 9.50

  Thanks for any ideas.

  -----Original Message-----
  From: accessd-bounces at databaseadvisors.com
  [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Hale, Jim
  Sent: Tuesday, November 14, 2006 3:59 PM
  To: 'Access Developers discussion and problem solving'
  Subject: Re: [AccessD] Carry Forward in a query

  If I understand what you want to do, I have done this with a non
  cartesian
  join. I create a table with a one field record and  one record for each
  date. In the query window I place this table and the table with the
  record
  you want to duplicate WITHOUT ANY JOIN. the resulting data set produces
  one
  record for each date. HTH
  Jim Hale

  -----Original Message-----
  From: Kaup, Chester [mailto:Chester_Kaup at kindermorgan.com]
  Sent: Tuesday, November 14, 2006 3:13 PM
  To: Access Developers discussion and problem solving
  Subject: [AccessD] Carry Forward in a query

  I have a table of data that has a record about every 6 months. I would
  like to generate a record for every month using the previous months data until there is data for the current records month. I can do this in code but had a coworker ask if it could be done in a query. I am not coming up with an answer. Your thoughts please. 

  Chester Kaup