A.D.Tejpal
adtp at touchtelindia.net
Sat Sep 10 00:50:32 CDT 2005
Chester,
It transpires that your data is as per individual dates and you are seeking a query that will show against each date, the moving total for a span of three months covering the current month as well as two months prior to the current month.
Sample query given below, should get you the desired results. Qty is the number type field being totaled while SDate is date type field in table T_Data. This can be adapted suitably for your specific situation.
A.D.Tejpal
--------------
======================================
SELECT T_Data.*, (Select Sum(Qty) From T_Data As T1 Where 12 * Year(T1.SDate) + Month(T1.SDate) >= 12 * Year(T_Data.SDate) + Month(T_Data.SDate) - 2 And 12 * Year(T1.SDate) + Month(T1.SDate) <= 12 * Year(T_Data.SDate) + Month(T_Data.SDate)) AS MovingTotal
FROM T_Data
ORDER BY T_Data.SDate;
======================================
----- Original Message -----
From: Kaup, Chester
To: Access Developers discussion and problem solving
Sent: Thursday, September 08, 2005 21:50
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([Date])),[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