OCQ Exercise 4 – AP/GL Reconciliation

Introduction

In this exercise, you will create a simple reconciliation between Accounts Payable and General Ledger. You will learn about 'distinct' queries, the TSSum function and reinforce techniques learned in earlier exercises.

Note - This tutorial example is designed to work with the Timberline Construction Sample Data.

Step 1 - Insert the List of Accounts

This example uses a query to produce a list of accounts that have been used in the Accounts Payable application. We can tell that an account has been used in AP because there will be transactions posted against it.

Note - Although the AP Transaction table contains many rows, this query only returns a few. This is a result of selecting only the "Accounts Payable Account" column and excluding duplicate rows. We therefore get a list of distinct accounts instead of a row for every transaction.

Step 2 - Add Account Title and Balance Columns

Now that you have the relevant list of GL Account numbers from the AP Transaction table you can use the Office Connector Lookup Wizard to insert a TSLookup function that displays the account title and current balance from the GL Account table.