[AccessD] FW: DateDiff problem in a query

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




More information about the AccessD mailing list