Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: MTD + YTD calculation

    Hello All;

    I have a table (tbl_MonthEndStats) that stores performance data for employees. One of the fields is Date. When the date data is entered, its entered as MM/30/31/YYYY. The monthly data is only entered once per month. I have a report, based on a query of the table, that I would like to list the current month’s performance, as well as the previous month’s, as well as the YTD data. I have created a query that retrieves the data for the current month, however I am stuck on how to get the Previous month and the YTD to populate to the form. I’m assuming I need a date calculation field in the query in order to populate the report, but I’m just stuck. Can anyone push me in the right direction?

    Report Example:

    Current Month Previous Month YTD

    Metric 1 Metric 1 Metric 1
    Metric 2 Metric 2 Metric2
    Metric 3 Metric 3 Metric3

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Say you have the following table design

    tblData
    dtDate (Date)
    lngValue (Long)

    You can use the following SQL to return the following:
    PrevDate: - record with the most recent date that is less that the current record
    PrevValue: - value for the sam record returned by PrevDate
    YTD: Sume of values for all records that are equal to and less than the current record that are in the same year.

    qyrPrevData
    Code:
    SELECT tblData.dtDate, tblData.lngValue, 
                 (SELECT TOP 1 a.dtDate 
                               FROM tblData AS a 
                               WHERE a.dtDate < tblData.dtDate 
                               ORDER BY a.dtDate DESC) AS PrevDate, 
                 (SELECT TOP 1 a.lngValue 
                               FROM tblData AS a 
                               WHERE a.dtDate < tblData.dtDate 
                               ORDER BY a.dtDate DESC) AS PrevValue, 
                 (SELECT Sum(a.lngValue) 
                               FROM tblData AS a 
                               WHERE (a.dtDate <= tblData.dtDate) 
                               AND (Year(a.dtDate) = Year(tblData.dtDate))) AS YTD
    FROM tblData;
    The above SQL will select the most recent date that is less than the current date. For the first record, it won't return anything since there is no date less it, assuming that it is the oldest date.

    If there are breaks in data it will return the record with the closest previous date. That is if there is a date for January though July but is missing June. The previous date shown for June will be May.

    If you want to only select the previous month, you can use DateDiff to return a value only if there was data for the preceeding month.

    qryPrevMonth
    Code:
    SELECT tblData.dtDate, tblData.lngValue, 
                (SELECT a.dtDate FROM tblData AS a 
                               WHERE (DateDiff("m",a.dtDate, tblData.dtDate) = 1)) 
                AS PrevMonth, 
                (SELECT a.lngValue 
                               FROM tblData AS a 
                               WHERE (DateDiff("m",a.dtDate, tblData.dtDate) = 1)) 
                AS PrevValue, 
                (SELECT Sum(a.lngValue) 
                               FROM tblData AS a 
                               WHERE (a.dtDate <= tblData.dtDate) 
                               And (Year(a.dtDate) = Year(tblData.dtDate))) 
                AS YTD
    FROM tblData;
    Hope that helps!
    Last edited by Cosmos75; 08-07-06 at 00:12.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Jul 2005
    Posts
    39
    If you have tables like

    tbl_MonthEndStats (empNo, date, empJob) and
    tbl_FormDate (myDate) this is reference date for report query

    would a query such as this help?

    Code:
    SELECT tbl_MonthEndStats.empNo, Sum(IIf(Month([date])=Month([myDate]) And Year([date])=Year([myDate]),1,0)) AS jobThisMonth, Sum(IIf(Month([date])=Month([myDate])-1 And Year([date])=Year([myDate]),1,0)) AS jobLastMonth, Sum(IIf([date] Between [myDate] And [myDate]-366,1,0)) AS jobYearToDate
    FROM tbl_MonthEndStats, tbl_FormDate
    GROUP BY tbl_MonthEndStats.empNo;

  4. #4
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks for the replys Taurus and Casmos75. I will digest this and get back to you.

    Larry

  5. #5
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Larryg,

    I created an article and sample, thought it might help.
    - Previous Date & Sum of Month/Year to-date using a correlated subquery

    Feel free to let me know if there is anything I can do to make it better or if you find any mistakes!

    Hope that helps!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •