Calculations In Reports

[Njliven]

I have a somewhat complicated problem. I have been asked to create a database based on the spreadsheet. It has lots of calculations that I am not sure are possible in Access. So any help is appreciated.


Employee Table: empid, empname
Employee OHHours Table: empOHID, Dept, Date, OHhours1, OHhours2, OHhours3, OHhours4, OHhours5. -This table one to many with Employees
Project Hours Table: EmpOHID, ProjectID, ProjectHours - This table one to many with Employee OHhours

Here is the problem. I have employees overhead hours per month, which consists of several types of overhead hours. This is only one line per month, but I also have project hours which can entail several projects. I have a report with a sub report that totals these out by employee, then by department. On the employee level I can get the a total of overhead hours and project hours. But on the Department level where it needs to adds the hours together for all the employees in the department I can't seem to bring over the project hours.
Here is the formula for the employee level which is working. =[Total_Overhead]+Nz([rpt_MONTHLY_SubRpt].[Report].[ProjectHours])
When I tried to bring it down the department level, it did not recognize the subreport and bring back only the Total_Overhead.


my formula is currently this =Sum([Total_Overhead]+Nz([rpt_PROJECT_TOTALS_sub].[Report].[SumofProjectHours]))
SumofProjectHours comes from a hidden subreport I based off a query totaling the project hours. I put the subreport in the Department footer along with the other totals. I have spent two whole days trying to figure out what is wrong. I also need this same calculation on a grand total level for the whole report. There are also two other calculations on the employee level that work, but I have not been able to get them to work on the department level either. I sure I have something in the wrong place or something not right, I just can't seem to figure it out. Thanks for any help I can get on this.
I have uploaded a screen shot of the spreadsheet I working from.

[June7]

Seeing spreadsheet doesn't help much. It is not a normalized db structure. Even your EmployeeOHHours table is not a fully normalized structure as indicated by multiple similar name fields.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Njliven]

Database is attached.

[June7]

This expression works to pull value from subform:
=Nz([rpt_PROJECT_TOTALS_sub].[Report].SumOfProjectHours])

This also works:

=Sum([Total_Overhead])+Nz([rpt_PROJECT_TOTALS_sub].[Report].[SumofProjectHours])

[Njliven]

This expression works to pull value from subform:
=Nz([rpt_PROJECT_TOTALS_sub].[Report].SumOfProjectHours])

This also works:

=Sum([Total_Overhead])+Nz([rpt_PROJECT_TOTALS_sub].[Report].[SumofProjectHours])

When I tired the first one I get the results #Size! in the field. I must have tried this before, because I got this error before in my attempt to make this work. Not sure why I am getting this. I tried to change the field from general number to fixed, then to standard, but that did not help.
On the second one I get an invalid syntax error.

[June7]

All I can say is those exact expressions worked for me in the db you posted.

[Njliven]

All I can say is those exact expressions worked for me in the db you posted.

I changed it so there would always be at least a zero total for the project side, this took care of the #size! error, which had something to do with the fact that there was not always a project hours total.
I got the formula you provided to give me a number, but it is only the overhead total which is 96. The total for the overhead plus the project total should be 516 for the CADD category. So it is not bring over the total from the sub report.

[June7]

The total I got was 116 (96 + 20).

[Njliven]

The total I got was 116 (96 + 20).

20 is just one of the project hours from one employee, I need the total for all employees in the category. which would be 96 + 420 for a total of 516. I did get it to bring back the 20. I tried to add sum to the beginning of the formula, but then no longer recognizes it.

I am thinking the underlying query must not be set up right, any suggestions there? I am been working on this all afternoon and no luck.

[IrogSinta]

I'm not sure why you have so many subreports for this. At the most, you really only needed 1 subreport. Now, if you make use of running sums, you don't even need a subreport. I've attached your revised db that demonstrates this method.

Ron

[Njliven]

I'm not sure why you have so many subreports for this. At the most, you really only needed 1 subreport. Now, if you make use of running sums, you don't even need a subreport. I've attached your revised db that demonstrates this method.

Ron

I want to thank you a million + times for all you help on this. It really is greatly appreciated.