YTD Subquery is not including December why?

  • Thread starter Thread starter jeromez
  • Start date Start date
J

jeromez

Hi Allen:

I created a query with a subquery to sum the hours worked by period (i.e.,
month) and then YTD hours next to the month hours.

However, it's not entirely working for me and it's driving me crazy.

The query gives me by Dept and period the ytd hours in the field labeled
"YTDHours"

One final glitch remains:

I added one record for December (period 12) and it is "blank" in the
YTDHours column.

Why doesn't it pick up December?

Any help would be greatly appreciated!

if it would be helpful to look at the database I can send it if you send me
an email.
or I can past the code.
 
Hi Allen:

I created a query with a subquery to sum the hours worked by period (i.e.,
month) and then YTD hours next to the month hours.

However, it's not entirely working for me and it's driving me crazy.

The query gives me by Dept and period the ytd hours in the field labeled
"YTDHours"

One final glitch remains:

I added one record for December (period 12) and it is "blank" in the
YTDHours column.

Why doesn't it pick up December?

Any help would be greatly appreciated!

if it would be helpful to look at the database I can send it if you send me
an email.
or I can past the code.
Click to expand...

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.
 
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.
Click to expand...
John W. Vinson [MVP]
Click to expand...

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 <
Click to expand...

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 =(
 
one more note:

I'm only using two departments in the example:

1. 405 which is the same as 4051
2. 455 which is the same as 4552

I initially started with 405 and 455 and thought it might be easier to
understand the example if added an additional number to the dept. but didn't
change all the data in the example before I posted and it was permanently
etched in time :P
 
Jeromez -

The following cannot evaluate to a date, since there aren't 13 months in a
year:

A.RepDate < DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

Try converting it to this:

A.RepDate < DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

to this:

A.RepDate < IF(Month(Tbl_Hours_Actual.RepDate) = 12,
DateSerial(Year(Tbl_Hours_Actual.RepDate)+1,
,1,0),DateSerial(Year(Tbl_Hours_Actual.RepDate),
Month(Tbl_Hours_Actual.RepDate)+1,1)) AS YTDHours

(this is untested, but you get the idea...)

--
Daryl S


jeromez said:
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.
Click to expand...
John W. Vinson [MVP]
Click to expand...

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 <
Click to expand...

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 =(
Click to expand...
 
Actually DateSerial will handle month 13 easily.

DateSerial(2009,13,1) will return January 1, 2010 (the 13th month of 2009 is
the first month of 2010)

It will also handle
DateSerial(2009,13,32) and return February 1, 2010

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top