Totalling grouped sub reports (1 Viewer)

[AlliCarr]

Hi

I have a report which contains two sub reports. Both sub reports are grouped on the same field. I would like to be able to total the group totals from each sub report and show this in the main report so I can see a total for each group from each sub report.

Is this possible??

Hope it makes sense but let me know if anything need clarification.

 

[arnelgp]

on each subreport, add an Unbound textbox, on the Report's Report Footer that will total the group:
for sub-report1, name it txtSumSub1, for sub-report2, txtSumSub2:

=Sum([TheFieldNameToSum])

then you can add an 2 unbound textbox to the main form to show those sub-totals:

=[subreportName]!txtSumSub1 and

the other

=[subreportName]!txtSumSub2 and

 

[June7]

Arnel, am guessing inclusion of word "and" with each expression was typo.

 

[DHookom]

Add the data to the record source of your main report. Let's say your main report is the employee table and the subreports are based on Employee Hours and Employee Payroll. Create to totals queries of Employee Hours and Employee Payroll that group by employee. Add these two queries to your main report's record source and join on the employee number. You now have total hours and total payroll in the main report.

 

[Mark_]

In case the previous solutions don't work, you can also use DSum to return the values you need.

DHookom's solution is the most versatile as it will also allow you to filter your report to skip parent records that you are not interested in. Think "Omit all customers who do not have an outstanding balance" or "Omit customers who have less than X in sales for this period".

 

[Ken Sheridan]

As a variation on DHookom's solution you might be able to aggregate the relevant values in subqueries in a single query, rather than joining multiple queries. The following query is a simple example which, in addition to the individual transaction details, returns the sums of the credit and debit transactions by each customer:

SELECT
    Customers.CustomerID,
    [FirstName] & " " & [LastName] AS Customer,
    TransactionDate,
    Credit,
    Debit,
    (
        SELECT
            SUM(Credit)
        FROM
            Transactions AS T2
        WHERE
            T2.CustomerID = T1.CustomerID
    ) AS TotalCustomerCredit,
    (
        SELECT
            SUM(Debit)
        FROM
            Transactions AS T3
        WHERE
            T3.CustomerID = T1.CustomerID
    ) AS TotalCustomerDebit
FROM
    Customers
    INNER JOIN Transactions AS T1 ON Customers.CustomerID = T1.CustomerID
ORDER BY
    LastName,
    FirstName,
    TransactionDate;

The above aggregated values could easily be returned in a customer group footer of course, without the use of subqueries, but returning the values in each row of the query's result table gives greater flexibility over the positioning of the controls.