YTD Subquery is not including December why?
in [Help]
|
From: Ken Snell [MVP] on 26 Nov 2009 17:17 Do you have a value of 12 for CalPeriod in both tables in your query? FROM Tbl_Hours_Actual INNER JOIN Tbl_Std_Hours ON Tbl_Hours_Actual.CalPeriod = Tbl_Std_Hours.CalPeriod The above will not select any records with 12 as CalPeriod value if that 12 value is missing from one of the tables. -- Ken Snell http://www.accessmvp.com/KDSnell/ "jeromez" <jeromezilincik(a)gmail.com> wrote in message news:b779dfb6-e6ff-4562-86b6-b96e3646e8ab(a)l2g2000yqd.googlegroups.com... >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] > > > > > > > > > > > > > >
From: Bob Quintal on 26 Nov 2009 17:34 jeromez <jeromezilincik(a)gmail.com> wrote in news:7283eb5b-3deb-46ab-af19-9236b8448f95(a)r24g2000yqd.googlegroups.co m: > Here's the code: [snip] Your problem is here: WHERE A.RepDate >= DateSerial(Year(Tbl_Hours_Actual.RepDate),1,1) [snip] You are asking for 2009. There would not be any december 2009 actual hours yet. -- Bob Quintal PA is y I've altered my email address.
From: jeromez on 27 Nov 2009 06:07 Hi Ken: Thanks for responding, [quote] >On Nov 26, 5:17 pm, "Ken Snell [MVP]" <kthsneisll...(a)ncoomcastt.renaetl> wrote: > Do you have a value of 12 for CalPeriod in both tables in your query? [snip] > The above will not select any records with 12 as CalPeriod value if that 12 > value is missing from one of the tables. [/quote] I do have a value of 12 for CalPeriod (i.e., Calender Period) in both tables. The purpose was to lookup the period in the standard hours table and divide that standard by the actual hours to get the FTE number (e.g., 400 actual hours for the month of December would look up CalPeriod 12 in the standard table and divide by 100, assuming 100 was in period 12) So that won't work? I do have a value of 12 in both tables. I see after posting the sample data tables it wrapped the tables making it hard to see, and I left out the CalPeriod Column (but it is in the database) Bob: [quote] >On Nov 26, 5:34 pm, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: > > Here's the code: > > [snip] > > Your problem is here: > > WHERE A.RepDate>= DateSerial(Year(Tbl_Hours_Actual.RepDate),1,1) > > [snip] > > You are asking for 2009. There would not be any december 2009 actual > hours yet. [/quote] Hi Bob: thanks for responding [quote] > You are asking for 2009. There would not be any december 2009 actual hours yet [/quote] At first I might have thought that was true, but I did some troubleshooting and reentered the code in another sample database and everything worked perfectly. Even when I entered December 2009 data the YTD column populated December and then I even went and entered 2010 dates and then it start accumulating in 2010 from Jan forward. Something is seriously wrong and because of that test it's driving me crazy, because I'm so close, yet so far away :P Any chance you or can could actually look at the database? and the other database that works properly? Peace, J
From: Ken Snell [MVP] on 27 Nov 2009 13:23 Do you see the December record if you run this query: SELECT Year([Tbl_Hours_Actual].[RepDate]) AS TheYear, Month ([Tbl_Hours_Actual].[RepDate]) AS TheMonth, Tbl_Hours_Actual.DeptNo, (Tbl_Hours_Actual.Hours) AS ActualHours, Tbl_Std_Hours.Hours, [Tbl_Hours_Actual].[RepDate] FROM Tbl_Hours_Actual INNER JOIN Tbl_Std_Hours ON Tbl_Hours_Actual.CalPeriod = Tbl_Std_Hours.CalPeriod -- Ken Snell http://www.accessmvp.com/KDSnell/ "jeromez" <jeromezilincik(a)gmail.com> wrote in message news:7484daae-a697-467a-9907-e16f6eb7ced4(a)a32g2000yqm.googlegroups.com... Hi Ken: Thanks for responding, [quote] >On Nov 26, 5:17 pm, "Ken Snell [MVP]" <kthsneisll...(a)ncoomcastt.renaetl> >wrote: > Do you have a value of 12 for CalPeriod in both tables in your query? [snip] > The above will not select any records with 12 as CalPeriod value if that > 12 > value is missing from one of the tables. [/quote] I do have a value of 12 for CalPeriod (i.e., Calender Period) in both tables. The purpose was to lookup the period in the standard hours table and divide that standard by the actual hours to get the FTE number (e.g., 400 actual hours for the month of December would look up CalPeriod 12 in the standard table and divide by 100, assuming 100 was in period 12) So that won't work? I do have a value of 12 in both tables. I see after posting the sample data tables it wrapped the tables making it hard to see, and I left out the CalPeriod Column (but it is in the database) Bob: [quote] >On Nov 26, 5:34 pm, Bob Quintal <rquin...(a)sPAmpatico.ca> wrote: > > Here's the code: > > [snip] > > Your problem is here: > > WHERE A.RepDate>= DateSerial(Year(Tbl_Hours_Actual.RepDate),1,1) > > [snip] > > You are asking for 2009. There would not be any december 2009 actual > hours yet. [/quote] Hi Bob: thanks for responding [quote] > You are asking for 2009. There would not be any december 2009 actual hours > yet [/quote] At first I might have thought that was true, but I did some troubleshooting and reentered the code in another sample database and everything worked perfectly. Even when I entered December 2009 data the YTD column populated December and then I even went and entered 2010 dates and then it start accumulating in 2010 from Jan forward. Something is seriously wrong and because of that test it's driving me crazy, because I'm so close, yet so far away :P Any chance you or can could actually look at the database? and the other database that works properly? Peace, J
From: jeromez on 27 Nov 2009 13:40 On Nov 27, 1:23 pm, "Ken Snell [MVP]" <kthsneisll...(a)ncoomcastt.renaetl> wrote: > Do you see the December record if you run this query: > > SELECT Year([Tbl_Hours_Actual].[RepDate]) AS TheYear, Month > ([Tbl_Hours_Actual].[RepDate]) AS TheMonth, Tbl_Hours_Actual.DeptNo, > (Tbl_Hours_Actual.Hours) AS ActualHours, > Tbl_Std_Hours.Hours, [Tbl_Hours_Actual].[RepDate] > FROM Tbl_Hours_Actual INNER JOIN Tbl_Std_Hours ON > Tbl_Hours_Actual.CalPeriod = Tbl_Std_Hours.CalPeriod > > -- > > Ken Snellhttp://www.accessmvp.com/KDSnell/ Hi Ken: I will test it out Monday when I return to work as I have Access at work and not at home on my Mac. Let's keep our fingers crossed that it works =) Have a great weekend.
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 4 Prev: New features of Access 2010 Next: Can't change the data type |