Prev: New features of Access 2010
Next: Can't change the data type
From: Ken Snell [MVP] on 27 Nov 2009 14:33
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 Snell
http://www.accessmvp.com/KDSnell/



"jeromez" <jeromezilincik(a)gmail.com> wrote in message
news:611b7c04-89c2-496b-b6f1-c04c5a220824(a)b2g2000yqi.googlegroups.com...
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.


From: jeromez on 27 Nov 2009 17:23
On Nov 27, 2:33 pm, "Ken Snell [MVP]"
<kthsneisll...(a)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
From: Bob Quintal on 27 Nov 2009 18:45
jeromez <jeromezilincik(a)gmail.com> wrote in
news:7484daae-a697-467a-9907-e16f6eb7ced4(a)a32g2000yqm.googlegroups.co
m:

> 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 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.

--
Bob Quintal

PA is y I've altered my email address.
From: jeromez on 27 Nov 2009 19:53

>
> 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

From: jeromez on 30 Nov 2009 06:30
Bob:

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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: New features of Access 2010
Next: Can't change the data type