Bobby Heid
bheid at appdevgrp.com
Wed Nov 15 13:27:51 CST 2006
It can in SQL Server according to this article: http://builder.com.com/5100-6388_14-6132748.html?tag=nl.e601 Not sure if it can be done in Access. Bobby -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester Sent: Wednesday, November 15, 2006 1:43 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Carry Forward in a query 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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com