using a subquery for YTD and Month by Dept

[jeromez]

YTD subquery for Not working and causing me grief

Help!

I wrote a query which is supposed to add MONTH and YTD hours BY DEPT BY PERIOD BY YEAR

The data pulls from the main table "Tbl_Hours_Actual" and then I'm trying to write a subquery off that table, but the YTD totals are goofy. If I remove "DeptNo" then YTD totals are fine. But I need it by Department.

Am I asking for too much in one query?

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;

 

[jeromez]

Hi, I attached a copy of the table hoping someone might take a few mintues to help (see Tbl_Hours_Actualz.xls).

I'm running into trouble in ACCUMULATING data by dept for each month for the field "hours"


I've also attached what I need ACCESS TO do which I've already accomplished using EXCEL (see file "Final_Results.xls)

Is this possible or should I just go back and forth between Excel and Access?

Thanks!

 

[boblarson]

How about this sample (using your spreadsheet) - see the query

 

[jeromez]

Bob:

Thanks so much for taking the time to help.

Yes, the query you wrote accomplishes part of the task I was looking for (actually Part A. in the new file I've attached called "Final_Result_with_Comments.xls")

However, the second table I need is the Part B (also in file "Final_Result_with_Comments.xls") or if it can somehow be a second column in "qrySample" that's cool also)

I've also uploaded the sample database you created and I added a query I had already written called "Qry_Pivot" which again only accomplished Part A. (see file called SAMPLE-QueryByMonthForDeptmodified.mdb)

Thanks so much for helping!

Warm Regards,
Jerome

 

[jeromez]

Okay, I think I almost have it.
See attached database "Almost_Final.mdb"

and look at "Query 1"

This 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 you can see in "Query 1" that it is "blank" in the YTDHours column?

Why doesn't it pick up December?


PS. I guess what I was missing (and discovered by trial and error) was that I needed to include "A.DeptNo = Tbl_Hours_Actual.Dept" number in the WHERE clause of my subquery (part of my original post)

 

[boblarson]

Okay, I think I almost have it.
See attached database "Almost_Final.mdb"

and look at "Query 1"

This 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 you can see in "Query 1" that it is "blank" in the YTDHours column?

Why doesn't it pick up December?


PS. I guess what I was missing (and discovered by trial and error) was that I needed to include "A.DeptNo = Tbl_Hours_Actual.Dept" number in the WHERE clause of my subquery (part of my original post)

It shows me December in Query 1 and in the pivot. If you look at the YTD for ALL records, the first month in there it does not show the value of the FIRST month. It only shows it in the second month and on. I haven't looked close enough to see why but that would tend to make me think your sub query isn't pulling the correct totals then.

 

[jeromez]

I added one record for December (period 12) and you can see in "Query 1" that it is "blank" in the YTDHours column?

Hi Bob:

I agree the subquery is still not right.

When I remove "A.Deptno = Tbl_Hours_Act.DeptNo" and just sum YTD by time without the DeptNo field then everything is fine.

Everything adds up except the last month of the year (i.e., December) in the YTDHours field of "Query 1" except December.

Somehow I need to get the WHERE statement to sum the hours by time and by DeptNo

 

[jeromez]

I still don't have any resolution with my minor problem so I was hoping someone could look at the two databases attached:


1 send.mbd contains the query "Qry_One" which you can see has a "blank" for the Dec period

2. Jerome.mdb which I created to test the formula on the subquery and it works fine

what's the difference between the two databases? I can't figure it out for the life of me and I was able to create both.

Help.

Jerome

jerome z @ hotmail dot com

 

[jeromez]

send. mbd is attached it looks like it didn't get attached to previous post

 

[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! =)