Please post the code (the SQL view of the query) and a description of the
structure of your table. If the table has a separate field for each month
("period"...?) then it's not correctly designed.
Hi John:
here the code in which I'm using a subquery to add a "YTD" column next to
the month column:
SELECT Year([Tbl_Hours_Actual].[RepDate]) AS TheYear, Month
([Tbl_Hours_Actual].[RepDate]) AS TheMonth, Tbl_Hours_Actual.DeptNo,
Sum(Tbl_Hours_Actual.Hours) AS MonthHours,
(SELECT Sum(A.Hours) AS YTD FROM Tbl_Hours_Actual AS A WHERE A.RepDate
= DateSerial(Year(Tbl_Hours_Actual.RepDate),1,1) AND A.RepDate <
DateSerial(Year(Tbl_Hours_Actual.RepDate), Month
(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours, Tbl_Std_Hours.Hours,
[MonthHours]/[Tbl_Std_Hours]![Hours] AS FTE
FROM Tbl_Hours_Actual INNER JOIN Tbl_Std_Hours ON
Tbl_Hours_Actual.CalPeriod = Tbl_Std_Hours.CalPeriod
GROUP BY Year([Tbl_Hours_Actual].[RepDate]), Month([Tbl_Hours_Actual].
[RepDate]), Tbl_Hours_Actual.DeptNo, Tbl_Std_Hours.Hours;
----------------------------
Here's an example of Tbl_Hours_Actual:
DeptNo Date Hours Name
405 10/01/2009 100 D. Duck
405 11/01/2009 100 J. Doe
455 10/01/2009 100 M Mouse
405 12/01/2009 100 D. Duck
405 11/01/2009 100 S. Clause
An Example of Tbl_Std_Hours:
CalPeriod Month Hours
1 Jan 100
2 Feb 100
3 Mar 105
4 Aor 110
5 May 100
6 Jun 100
7 jul 105
8 Aug 100
9 Sep 110
10 Oct 100
11 Nov 100
12 Dec 100
Final Results NEED to look like:
DeptNo Year Month MonthHours YTDHours FTE
4051 2009 Oct 100 100 1.0
4051 2009 Nov 200 300 2.0
4051 2009 Dec 100 400 1.0
4552 2009 Nov 100 100 1.0
[you can see as it finishes one dept. it starts the next and starts the
accumulation of hours again by period]
BUT for some reason it's looking like this:
DeptNo Year Month MonthHours YTDHours FTE
4051 2009 Oct 100 100 1.0
4051 2009 Nov 200 300 2.0
4051 2009 Dec 100 [blank] 1.0
4552 2009 Nov 100 100 1.0
[notice the "400" is missing or blank under the "YTDHours"
column for Dept 405 at the year-end in December]
Also if I put sample data in for FY2010 it starts up again fine in Jan 2010
and shows December 2009 blank.
doesn't make sense, also i've done some trouble shooting, and the subquery
code seems fine because it simply copies the table "Tbl_Hours_Actual" AS "A"
and then looks at the period from the beginning of the year until the period
and sums it up.
I'm at my wits end since I'm close and it works fine for each month except
december =(