Solved Totalling grouped sub reports

[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.

 

[AlliCarr]

Thanks for your replies, I think the main problem I am having is that the main report does not have a data source and is just there to combine the sub reports.

Each sub report brings in data from unrelated tables but I'm thinking that, as they are so similar, I should probably combine the tables and then the issue of totalling groups in a report disappears. Don't know why I didn't just do that in the first place.

Sometimes you can't see the wood for the trees I guess!

 

[Ken Sheridan]

I should probably combine the tables

If data is being encoded in table names you should certainly do that. A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as single values at column positions in rows in tables, and in no other way. This is not an uncommon design fault. We often find data such as an accounting year has been encoded in table names, e.g. CurrentAccount _2024_25, CurrentAccount_2025_26.

Coalescing such tables into a single table is simple to do with a UNION ALL query, in which, in addition to all the identical columns from each current table, a column with a literal value is included in each part of the UNION ALL operation. In the above example the string expressions "2024-25" and "2025-26" would be used. The result table of the UNION ALL query can then be inserted, by means of an 'append' query, into an empty CurrentAccounts table whose structure is the same as the current multiple tables, but with the addition of an AccountingYear column, in this example of short text data type.