On a report I need the totals from different groups in text boxes

[Jessica240]

Hello.. Pretty simple problem here.

I have a report that shows how much we've paid one of our employees for work they've done in different departments. For accounting purposes, I need to know how much of their pay to allocate to each different department.

I have grouped the data by department and have the total for each group in the group footer.

But now I need to isolate those totals and use them in text boxes at the bottom of my report so that I can use their values in other calculations.

Usually an employee doesn't work in more than 3 or 4 departments so I only need 4 text boxes at the bottom, one for each department.



I have the text boxes in the report footer. I need to put an expression in each text box that results in the total for one department.

At first, I tried using the DSUM function, where it would sum the amount paid if the department matched some department-specific criteria.

Well that didn't work because we have 50 different departments so now I'm at the mercy of the experts.... any help is greatly appreciated!

[rpeare]

=sum(iif([Department] = "XXX", [AmountField], 0))

[rpeare]

Or... because you don't know what departments are going to be represented for the employee it would be easier to just create a sub report for the employee that lists the departments/sums the value(s) you want and put the subreport in the group footer of the the report.

[Jessica240]

I can create a sub-report. I've used those before.

But I still don't know how to get the sub-report to sum each department's totals in a text box that I can use for other calculations... Like you said, I can't use the sumif formulas because the departments will always be variable.

Isn't there some way I can tell Access I want the total from each group in its own text box?

[rpeare]

Are you printing a report for all employees, but on each employee's sheet you want the calculation we're talking about to appear, then at the end of the report sum all the different departments? or do you simply want to use the sum value for different calculations in your group footer? For instance let's say you have an employee that works in 3 different 'departments' but the pay scale is different in each of the three departments so you want to calculate their gross pay by multiplying their time in each department by the scale?

I guess I'm asking what are the 'other calculations' you're doing? Can they also be worked into the subreport?

[Jessica240]

Each employee has their own sheet which we print out and distribute to all of the employees. Then, we also give a copy to our accountant, who has to use the information at the bottom to do the accounting stuff. Currently, we do this in Excel, where we just have a sum formula for each department.

So now I'm trying to create the same thing in Access where the top of the report has all of the detail, then down at the bottom I want a summary that shows each department and what the total dollar is for that department.

I tried to put a picture in here so you can see how it looks in Excel. I need something that is similar in Access.

[Jessica240]

The other calculations are basically just a check. Accountants are obsessed with balancing numbers right and left. So I need to be able to add all of the group totals together to get the grand total, and compare that with the total from up above. If something is off, then those two numbers won't be the same.

[rpeare]

I don't understand why you can't do that in a subreport. At worst you would need 2 subreports. Assuming you have 10 employees and your report prints out all 10 employees you would need a subreport that is tied directly to the employee ID and would be in the employeeID footer. Then you would need an UNBOUND report (one that is not linked to anything) in your report footer that sums all the information over all employees.

[Jessica240]

Maybe this image will help.....

[Jessica240]

How do I tie the subreport to the employee ID?

[Jessica240]

I have a form with a combo box that lets you select which employee you are interested in. Then, you type in the check date, period beginning, period ending, etc. and then click "Open Report" - this then opens the report for that employee.

The queries I have set up, use the name selected from the combo box as the criteria, so that the report only has the pay data for that particular employee.

[Jessica240]

OK I figured it out. I created a query that groups the departments and sums the payable amounts. I will make a sub report based off of this query and put this sub report in my main report!

Wow I'm so glad we figured it out. If it weren't for your help I would still be scratching my head all night! - sorry it took me so long to get on the ball.

Thanks so much!!!!

[Jessica240]

Crap..... I tried putting the sub report in the main report and got this window:

"You can't use a pass-through query or a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings Properly."

What does that mean?????????????

[Jessica240]

Nevermind I just looked it up and solved the problem...

[rpeare]

well crap I had this example just worked out from your post way back when and now it's useless!!

Jessica240.zip