Back to Basics: Use the Join Task to Combine Two Databases
Back to Basics highlights key IDEA skills every auditor needs in their toolbox. This month’s blog showcases the easy to use Join task that enables you to combine databases.
Join is one of the most powerful tasks of IDEA Data Analysis Software. It provides an easy to use and flexible way to join databases. It is used mainly for two reasons:
- Combining fields from two databases into a single database for analysis.
- For testing data which matches or does not match across databases.
Use the Join task to reconcile data between two databases, such as bank statements or sales reports and invoices.
You can join or match databases only if they contain one or more common fields (referred to as match key fields). The match key fields do not need to have the same name or length, but they must be of the identical field type. The user can define up to 8 matching keys.
There are a few points to note while using the Join task:
- You can join only two databases at one time.
- IDEA joins the secondary database to the primary database. Make sure to combine the databases in the correct order.
- The databases must be in the same location, your Desktop project folder for instance.
IDEA provides five Join options. Be careful to choose the correct one. You can select:
- Matches only
- Records with no secondary match
- Records with no primary match
- All records in the primary file
- All records in both files
The Join task takes each record for every match key field in the primary database and looks for a matching record in the secondary database. Join does not support many-to-many relationships, so when you join two Character fields with different lengths, IDEA pads the shorter field contents with trailing spaces.
A Real Life Example of Using The IDEA Join Task
We have a record of a bank book maintained internally, and we have a separate record of bank statements as per bank records. Now we want to reconcile the data.
We can do so by joining the two files using several of the matching options. First, we open an internal bank book as the primary record and a bank statement as the secondary record. We will use the transaction ID as the matching field.
Then, we can perform 3 separate join operations, each creating a new results database in IDEA for analysis:
- Select records with no primary / secondary match to identify missing entries in either file. For instance, missing deposits from the Bank Statement.
- Select the matches-only option to identify any discrepancy in the records.
- After Joining all records, we can determine the difference in amount from the two different files by appending a calculated field in the joined database that will calculate the difference between the two amount columns in the joined databases.
Join task is one of the key capabilities an Auditor should master within IDEA. For more advice about using IDEA® Data Analysis Software, you can read our Tech Tips here. You can also view our Product page here.