Report Totals - Detail and Group

[thread]

Hi all - I have a report that consists of a list of people in a department who have made budget requests along with the payments for those requests. It is grouped by department, person, request id. The detail area holds the payments for each request. A request can have more than one payment. I need to have a total of requests by department and a total of the payments for the same department. The problem I am having is that because my report is based on a query, when I total my payments, I get the correct amount but when I total my requests, the report adds in a request for every payment instead of a single request - so:

Request 1 = $100
Payment 1 = $50
Payment 2 = $25
Total Requests = $200 (instead of the $100 I want)
and Total Payments = $75

How do I solve this problem?

 

[dhookom]

You can use a running sum on text boxes. However, I would create a totals query like:

SELECT Dept, Sum(Request) as DeptRequestSum
FROM tblRequests
GROUP BY Dept;

Then join this totals query to your report's record source query using the Dept fields. Where you need to see the dept sum of request, just bind DeptRequestSum to a text box. Don't sum it.

Duane
Hook'D on Access
MS Access MVP