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.
Next page: Resources
Be the first to know tips & tricks on business application development!

A confirmation e-mail has been sent to the e-mail address you provided .

Click the link in the e-mail to confirm and activate the subscription.