Another MTD/YTD Running Totals Question

[Randy Brown]

I have review many of the previous posts and tried many of the solutions and
I still can't come up with one that works for me. I am at wits end. Please
Help me while I still have hair.

My table is structured as follows:

TimesheetDetailID AutoNumber;
TimeSheetID Number;
EmployeeID Number;
ProjectID Number;
Hours Number;
TSDate Date/Time;

I am trying to get Month-to-Date and Year-to-Date Totals in the same query.
I have tried using DSUM, Nested Select and INNER Joins. Below are two
examples:

Nested Select Statement:

SELECT Year(tsdate) AS Yr, Month(tsdate) AS Mo,
Sum(TimeSheetDetail_v1.Hours) AS MTD, (SELECT sum(hours)
FROM TimeSheetDetail_v1
WHERE (TSDate <= TimeSheetDetail_v1.TSDate)) AS YTD
FROM TimeSheetDetail_v1
GROUP BY Year(tsdate), Month(tsdate)
ORDER BY Year(tsdate), Month(tsdate);

Nested Select Statement Results:

Yr Mo MTD YTD
2006 10 359 3416.89999961853
2006 11 470 3416.89999961853
2006 12 343.5 3416.89999961853
2007 1 496.899 3416.89999961853
2007 2 440.5 3416.89999961853
2007 3 736.5 3416.89999961853
2007 4 570.5 3416.89999961853

INNER JOIN Method:

SELECT Year(a.tsdate) AS Yr, Month(a.tsdate) AS Mo, Sum(a.Hours) AS MTD,
Sum(b.Hours) AS YTD
FROM TimeSheetDetail_v1 AS a
INNER JOIN TimeSheetDetail_v1 AS b
ON ( (a.TimeSheetDetailID = b.TimeSheetDetailID)
AND (b.tsdate >= a.tsdate)
AND (DateDiff("yyyy",a.tsdate,b.tsdate)=0) )
GROUP BY Year(a.tsdate), Month(a.tsdate)
ORDER BY Year(a.tsdate), Month(a.tsdate);

INNER JOIN Results:

Yr Mo MTD YTD
2006 10 359 359
2006 11 470 470
2006 12 343.5 343.5
2007 1 496.899 496.89999961853
2007 2 440.5 440.5
2007 3 736.5 736.5
2007 4 570.5 570.5

 

[Dale Fye]

How about:

SELECT Year(T.tsdate) AS Yr, Month(T.tsdate) AS Mo,
Sum(T.Hours) AS MTD,
(SELECT sum(hours) FROM TimeSheetDetail_v1
WHERE YEAR(T.tsDate) = Year(TimeSheetDetail_v1.TSDate)
AND Month(T.tsDate) <= Month(TimeSheetDetail_v1.tsDate)) AS YTD
FROM TimeSheetDetail_v1 T
GROUP BY Year(T.tsdate), Month(T.tsdate)
ORDER BY Year(T.tsdate), Month(T.tsdate);

HTH
Dale

 

[Randy Brown]

Thanks Dale! With your help I finally got the results I was looking for. I
also had to start Fiscal Year in October. See Below:

SELECT Year(T.tsdate) AS Yr, Month(T.tsdate) AS Mo, Sum(T.Hours) AS MTD,
(SELECT sum(hours) FROM TimeSheetDetail_v1
WHERE (IIF(month(T.tsDate)>=10,
(month(TimeSheetDetail_v1.tsDate) <= month(T.tsDate))
AND (month(TimeSheetDetail_v1.tsDate) >= 10),
(Year(TimeSheetDetail_v1.tsDate) <= Year(T.tsDate))
AND (month(TimeSheetDetail_v1.tsDate) >= 10)
OR (month(TimeSheetDetail_v1.tsDate) <= month(T.tsDate))
))) AS YTD
FROM TimeSheetDetail_v1 AS T
GROUP BY Year(T.tsdate), Month(T.tsdate);

Yr Mo MTD YTD
2006 10 359 359
2006 11 470 829
2006 12 343.5 1172.5
2007 1 496.89 1669.39
2007 2 440.5 2109.89
2007 3 736.5 2846.39
2007 4 570.5 3416.89