YTD Subquery is not including December why?
jeromez
(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.
jeromez
[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]
Salad
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.
Ken Snell [MVP]
that entered date is the one you expect.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
"jeromez" <jeromez...@gmail.com> wrote in message
news:7283eb5b-3deb-46ab...@r24g2000yqd.googlegroups.com...
jeromez
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]
Ken Snell [MVP]
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" <jeromez...@gmail.com> wrote in message
news:b779dfb6-e6ff-4562...@l2g2000yqd.googlegroups.com...
Bob Quintal
news:7283eb5b-3deb-46ab...@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.
jeromez
Thanks for responding,
[quote]
>On Nov 26, 5:17 pm, "Ken Snell [MVP]" <kthsneisll...@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...@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
Ken Snell [MVP]
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" <jeromez...@gmail.com> wrote in message
news:7484daae-a697-467a...@a32g2000yqm.googlegroups.com...
jeromez
<kthsneisll...@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.
Ken Snell [MVP]
It's a "debug" query to see if your original query is even selecting the
December data record.
--
Ken Snell
http://www.accessmvp.com/KDSnell/
"jeromez" <jeromez...@gmail.com> wrote in message
news:611b7c04-89c2-496b...@b2g2000yqi.googlegroups.com...
jeromez
<kthsneisll...@ncoomcastt.renaetl> wrote:
> The query that I posted is not a replacement for the one that you're using.
> It's a "debug" query to see if your original query is even selecting the
> December data record.
Awesome! I can't wait to try it out.
it sounds like you understand what I need the query to do: the
purpose is simply to add the hours from the Tbl_Hours_Actual table for
the "month" and "YTD" by department by year and month.
I think the 'thing' that made it complicated is:
1) I added the dimension of department as opposed to 'simply' adding
up the hours by year by month
2) I'm trying to lookup a denominator from "Tbl_Std_hours" based on
the period in "Tbl_HOurs_Actual" and compute a monthly AND YTD FTE
(i.e., Month Hours/Month Hours Standard AND also YTD HOurs/ YTD
Hours Standard)
ergo, in a perfect world my query would have the following information
in ONE query:
FIELDS:
1) Department
2) Year
3) Month (or Period)
4) Month Hours
5) YTD Hours
6) Month FTE
7) Month FTE
I'm thinking if we can put a man on the moon, I can't be asking for
too much =)
Peace,
J
Bob Quintal
news:7484daae-a697-467a...@a32g2000yqm.googlegroups.co
m:
> Bob:
>
> [quote]
>>On Nov 26, 5:34�pm, Bob Quintal <rquin...@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 hour
> s 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.
Yes, but does your main database have actuals for the year 2009?
If the database is small and in Access 2003, not 2007, decypher my
email and send it along. preferably WinZipped.
jeromez
>
> Yes, but does your main database have actuals for the year 2009?
>
> If the database is small and in Access 2003, not 2007, decypher my
> email and send it along. preferably WinZipped.
Hi Bob:
Yes main database (table "Tbl_Hours_Actuals) has the actuals for 2009.
I'm trying to figure out your email and then I will send. Since I'm
new at this forum I'm hoping I decipher it correctly
Jerome
jeromez
I don't think my email test made it to you. If you can email me
directly at jeromez @ hot mail dot com
Ken:
I'm going to run your debug query this morning when I get to work and
post the results
Peace,
Jerome
jeromez
<kthsneisll...@ncoomcastt.renaetl> wrote:
> The query that I posted is not a replacement for the one that you're using.
> It's a "debug" query to see if your original query is even selecting the
> December data record.
> --
>
> Ken Snellhttp://www.accessmvp.com/KDSnell/
Ken:
I ran your debug query and it did return the December records.
what now?
Is that good news?
Jerome
jeromez
PROBLEM SOLVED!
I knew I would get it if I kept looking, because it didn't make sense
one would work and the other subquery wouldn't
Well the problem was that in the table the "Data Type" for "RepDate"
field I incorrectly or inadvertently put "text" when obviously it
should have been "Date/Time"
thanks everyone for your help, now I can move forward! =)