Report Totals by Facility 1

[ajhts]

I have a report that looks like this:

Day Facility Hours
1 Four Seasons 3.5
1 Greencroft 2
2 Four Seasons 4
3 Nazareth 2
3 Greencroft 4

I want a grand total at the bottom of the report to show a break out with totals. Example:

Four Seasons 7.5 total hours
Greencroft 6 total hours
Nazareth 2 total hours

I keep getting a grand total without a breakout by facilty. How do I get it to show the results like my example above. Thank you very much!

AJ

 

[THWatson]

AJ
Try this...

In your report footer, put
Sum(Abs([Facility] = "Four Seasons"))

And repeat this for each of the results you wish, changing the part in quotation marks.

Tom

 

[dhookom]

I would use a subreport based on a totals query. This would automatically accomodate new facilities without maintaining controls in the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ajhts]

Thanks for both of your responses. This is kind of like a timesheet. There could be 10 pages to this report with each page being a single person with the facilities and hours worked. So I won't be able to change it every time to look for a certain facility as they could change.

Will a subreport with a totals query appear at the bottom of each page with the totals for that page/single person.
Hope I am explaining it right. The sample report that I showed you is for John Doe. Page number 2 might be Mary Smith and she might have a complete different set of facilities that she worked at.

Just trying to figure out if this can be done and a possible way to do it.

Thanks,
AJ

 

[dhookom]

A subreport could be in a group footer or report footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[ajhts]

DHook that worked great, I do have one more question. I added a new footer called Overtime Hours and it groups by week and gives me a total number of overtime hours at the end of the week in this footer. At the bottom of the page , I would like to have a grand total of overtime hours from the Overtime Footer. The name of the box is [Text58]. I thought I could just =sum([Text58]) and that would add all of the subtotals from the ot footer. When I try and run the report it asks me to fill in data for Text58. I don't know how else to add the subtotals from the footer for a grand total. I hope I explained this okay!
Thanks,
AJ

 

[dhookom]

You can't Sum() a control. Can't you just use the same expression/control source in your report footer as in your week footer?

BTW: You can't use Sum() in a Page Footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]