O'Connor, Patricia (OTDA)
Patricia.O'Connor at otda.state.ny.us
Thu Sep 8 14:27:15 CDT 2005
Try something like this.
SELECT t2.PID,
t2.Date,
t2.vm_wtri,
( Select Sum(t1.vm_wtri)
From [tbl One Well Water Injection] AS T1
Where T1.Date >= t2.Date - dateadd("m",-2,t2.Date)
And T1.Date <= t2.Date) AS MovingTotal,
dateadd("m",-2,t2.Date) AS [2 Months Prior],
DateDiff("d",dateadd("m",-2,t2.Date) ,t2.Date) AS [Days Diff]
FROM [tbl One Well Water Injection] as t2
Though I wasn't sure what dates you were using because of the names.
The column name was just date which is also what DATE() is. I never
name DATE columns with just DATE nor do I put spaces in names. An
example would be ReceiveDt or Receive_Dt - just makes things easier in
the long run
HTH
******************************************************************
*Patricia O'Connor
*Associate Computer Programmer Analyst
*OTDA - BDMA
*(W) mailto:Patricia.O'Connor at dfa.state.ny.us
*(w) mailto:aa1160 at dfa.state.ny.us
******************************************************************
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Kaup, Chester
> Sent: Thursday, September 08, 2005 12:21 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] FW: DateDiff problem in a query
>
> Tried your idea in the date DateDiff part of the query
> instead of DateSerial with the same erroneous results
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Elam, Debbie
> Sent: Thursday, September 08, 2005 10:59 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] FW: DateDiff problem in a query
>
> You can use months in Date functions. Try DateAdd("m", 1,
> Date) to get
> 1
> month from the Date.
>
> Debbie
>
>
> _____
>
> From: Kaup, Chester
> Sent: Thursday, September 08, 2005 10:39 AM
> To: 'Access Developers discussion and problem solving'
> Subject: DateDiff problem in a query
>
>
>
> I have the following query to calculate a moving total. The
> theory is to calculate a total of a given date and the 2
> prior dates. For example the row of the query with a date of
> 5/1/2005 should have a total of vm_wtri for March, April and
> May. I am using the DateSerial and DateDiff functions to go
> back 2 months. As you can see in the results some months this
> works and some it does not. The number of days back appears
> to be correct. What am I missing? Thanks for the assistance.
>
>
>
> SELECT [tbl One Well Water Injection].PID, [tbl One Well
> Water Injection].Date, [tbl One Well Water Injection].vm_wtri, (Select
> Sum(vm_wtri) From [tbl One Well Water Injection] AS T1 Where
> T1.Date >= [tbl One Well Water Injection].Date
> -DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Dat
> e])),[Date
> ]) And T1.Date <= [tbl One Well Water Injection].Date) AS
> MovingTotal,
> DateSerial(Year([Date]),Month([Date])-2,Day([Date])) AS [2
> Months Prior],
> DateDiff("d",DateSerial(Year([Date]),Month([Date])-2,Day([Date
> ])),[Date]
> ) AS [Days Diff]
>
> FROM [tbl One Well Water Injection]
>
> WHERE ((([tbl One Well Water Injection].Date)>=#1/1/1994#))
>
> ORDER BY [tbl One Well Water Injection].Date;
>
>
>
> The results:
>
>
>
> PID Date vm_wtri
> MovingTotal 2 Months Prior Days Diff
>
> 424150174300 9/1/2004
> 7/1/2004 62
>
> 424150174300 10/1/2004
> 8/1/2004 61
>
> 424150174300 11/1/2004 23546.37 23546.37
> 9/1/2004 61
>
> 424150174300 12/1/2004 61430.49 84976.86
> 10/1/2004 61
>
> 424150174300 1/1/2005 43755.91 128732.77
> 11/1/2004 61
>
> 424150174300 2/1/2005 45906.27 89662.18
> 12/1/2004 62
>
> 424150174300 3/1/2005 34813.93 124476.11
> 1/1/2005 59
>
> 424150174300 4/1/2005 44469.81 125190.01
> 2/1/2005 59
>
> 424150174300 5/1/2005 3881.54 48351.35
> 3/1/2005 61
>
> 424150174300 6/1/2005 3881.54
> 4/1/2005 61
>
> 424150174300 7/1/2005 3881.54
> 5/1/2005 61
>
> 424150174300 8/1/2005
> 6/1/2005 61
>
>
>
>
>
> 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
> - JENKENS & GILCHRIST E-MAIL NOTICE - This transmission may
> be: (1) subject to the Attorney-Client Privilege, (2) an
> attorney work product, or (3) strictly confidential. If you
> are not the intended recipient of this message, you may not
> disclose, print, copy or disseminate this information.
> If you have received this in error, please reply and notify the sender
> (only) and delete the message. Unauthorized interception of
> this e-mail is a violation of federal criminal law.
> This communication does not reflect an intention by the
> sender or the sender's client or principal to conduct a
> transaction or make any agreement by electronic means.
> Nothing contained in this message or in any attachment shall
> satisfy the requirements for a writing, and nothing contained
> herein shall constitute a contract or electronic signature
> under the Electronic Signatures in Global and National
> Commerce Act, any version of the Uniform Electronic
> Transactions Act or any other statute governing electronic
> transactions.
> --
> 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
>