How do I do a subtotal on a sort field?

[bmarkj]

I have created a report pulled from a query showing directors, films, release year and main character. I have grouped on the director and sorted on the film title, but I can't figure out how to return a subtotal of the number of films done by each director. Any thoughts??? I'm using Access 2013 in a Windows 8.1 environment. Thanks for any help anyone can provide!

-bmarkj

[ItsMe]

Perhaps you can use an unbound textbox control in the footer or header of one of your groups. You can place expressions in unbound text box controls. Perhaps an expression using the Sum() function will suffice.

=Sum(FieldName)

[bmarkj]

OK, I tried that. Sum wouldn't work, but Count did. However, it returns the total number of ALL films for each sort group, i.e., each sort group has the same number in it, and that number is the total of all films in the report. Hmmmm.....

[ItsMe]

You are correct to use the Count() method. I misspoke earlier.

So the footer for any given group is only good for that group. You discovered this. So the options are to create more groups or use code to get the info you need.

Outside of the report you could create additional queries to do calculations and then nest said query as a subquery.

Outside of the report you could possibly create another report and then nest said report as a subreport.