Query for a data set

The Trial balance accounting report is a table with its rows listing all the accounts in the chart of accounts, and its columns displaying the opening balance, turnover, and the closing balance for the debits and credits of each account.

1. Select the following tables as the data sources (fig. 16.20):
   • Main chart of accounts object (referential) table
   • Primary.BalanceAndTurnovers accounting register virtual table
   
   Fig. 16.20. Query tables
2. Select the Ref field from the Main table.
3. Select the following fields from the PrimaryBalanceAndTurnovers table (fig. 16.21):
   • SumOpeningSplittedBalanceDr
   • SumOpeningSplittedBalanceCr
   • SumTurnoverDr
   • SumTurnoverCr
   • SumClosingSplittedBalanceDr
   • SumClosingsplittedBalanceCr
   
   Fig. 16.21. Fields selected for the query
4. Click the Links tab and specify that you want to select all records from the main table and only those that match the link condition from the register table (fig. 16.22).
   
   
   Fig. 16.22. Table link condition
5. Click the Unions/Aliases tab and set the following aliases for the report fields (fig. 16.23):
   • Account
   • OpeningBalDr
   • OpeningBalCr
   • TurnoverDr
   • TurnoverCr
   • ClosingBalDr
   • ClosingBalCr
   
   Fig. 16.23. Unions/aliases
6. Click OK.
   
   This completes the query creation.