YTD Subquery is not including December why?
in [Help]
|
From: jeromez on 25 Nov 2009 21:22 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.
From: jeromez on 25 Nov 2009 21:40 Here's the code: [quote] 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; [/quote]
From: Salad on 26 Nov 2009 00:19 jeromez wrote: > Here's the code: > > [quote] > 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; > [/quote] > > Don't know. Why don't your select the records from Tbl_Hours_Actual and save as query1. Then perform your select on Tbl_Std_Hours and save as query2. Then join query1 to query2.
From: Ken Snell [MVP] on 26 Nov 2009 09:40 Assuming that it's a date in December that you added, check that the year of that entered date is the one you expect. -- Ken Snell http://www.accessmvp.com/KDSnell/ "jeromez" <jeromezilincik(a)gmail.com> wrote in message news:7283eb5b-3deb-46ab-af19-9236b8448f95(a)r24g2000yqd.googlegroups.com... > Here's the code: > > [quote] > 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; > [/quote] > >
From: jeromez on 26 Nov 2009 06:47
I can try that. 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: Period Month Hours 1 Jan 100 2 Feb 100 3 Mar 100 [continue for demonstration using 100 as the standard for all the months] Final Results NEED to look like: DeptNo Year Month MonthHours YTDHours FTE 405 2009 Oct 100 100 1.0 405 2009 Nov 200 300 2.0 405 2009 Dec 100 400 1.0 455 2009 Nov 100 100 1.0 BUT for some reason it's looking like this: DeptNo Year Month MonthHours YTDHours FTE 405 2009 Oct 100 100 1.0 405 2009 Nov 200 300 2.0 405 2009 Dec 100 1.0 455 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] |