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.
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.
Check the Exclude duplicate rows box and click Finish.
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.
"One of the more significant benefits to using Office Connector is the ability to 'search' for information. If someone is out of balance by a certain dollar amount, it is extremely simple using Office Connector to search for and find the reason why."