Summarising related data

Discussion created by orbitpro on Jan 14, 2019
Latest reply on Jan 15, 2019 by CarlSchwarz



I have tried to find the answer from previous discussions but I haven't yet been able to figure it out.


I have a database containing several tables.  Two of these tables (Deals and Accounts) are linked by a field called DealID.  One Deal can have many Accounts.  I record the income for a deal in the Deal table and record details about loan accounts in the Account table.


In the Deal table I have several fields, including Status, SettlementDate, Income and Summary_Income.  In the Accounts table I have fields called LoanAmount and Summary_LoanAmount.


I have a report that creates a found set based on settled deals within a date range.  I want to display the total income for all found records and the total amount of all loan accounts that relate to the found records.  My report layout shows records from the Deals table.


My report is a list view and the Summary_LoanAmount field totals the loan amount for a particular deal.  I want to add these all together for the found set of deals and show it on the report.


I hope this makes sense, and hopefully there is an easy solution.


I would appreciate any assistance.