[AccessD] Carry Forward in a query

Kaup, Chester Chester_Kaup at kindermorgan.com
Wed Nov 15 12:42:36 CST 2006


A fellow employee who does not write code asked if it could be done. I
am thinking not or certainly not the best way or practical way to go
about doing it. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Wednesday, November 15, 2006 11:11 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Carry Forward in a query

Chester,

Just out of curiosity...why does it have to be a query and not
code?...and 
in this scenario could call a custom function from the query?

Just curious,

Mark A. Matte


>From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "Access Developers discussion and problem 
>solving"<accessd at databaseadvisors.com>
>Subject: Re: [AccessD] Carry Forward in a query
>Date: Tue, 14 Nov 2006 16:40:39 -0600
>
>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
>
>Engineering Technician
>
>Kinder Morgan CO2 Company, LLP
>
>Office (432) 688-3797
>
>FAX (432) 688-3799
>
>
>
>
>
>No trees were killed in the sending of this message. However a large
>number of electrons were terribly inconvenienced.
>
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>***********************************************************************
>The information transmitted is intended solely for the individual or
>entity to which it is addressed and may contain confidential and/or
>privileged material. Any review, retransmission, dissemination or
>other use of or taking action in reliance upon this information by
>persons or entities other than the intended recipient is prohibited.
>If you have received this email in error please contact the sender and
>delete the material from any computer. As a recipient of this email,
>you are responsible for screening its contents and the contents of any
>attachments for the presence of viruses. No liability is accepted for
>any damages caused by any virus transmitted by this email.
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Try the next generation of search with Windows Live Search today!  
http://imagine-windowslive.com/minisites/searchlaunch/?locale=en-us&sour
ce=hmtagline

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





More information about the AccessD mailing list