Join the Hunt

You’ve used a database to search your client’s data for a red flag that might indicate fraud. You imported the data into a database and created relationships between table fields. Then you carefully created a filter to search for red flags. Finding nothing, you conclude that there is no evidence of fraud.

If you didn’t factor in join properties, you may need to reconsider your conclusion. Using a default join property, you might unknowingly be performing tests on an incomplete data set.

WHAT ARE JOIN PROPERTIES?
Relational databases store data in multiple, normalized tables. Everything about vendors, including names and addresses, is stored in a vendors table. Checks issued to those vendors are stored in another table. A relationship is created between a field—the Vendor ID, for example—from each table to bring the tables of data together to support data analysis. How the relationship joins the tables is known as the join property.

The most common type of join property, an inner join, displays only matches between the tables in the dynaset—a temporary combined view of selected data. An inner join using limited examples of checks and vendors tables is illustrated in Exhibit 1.

The tables are connected by a relationship between the Vendor ID fields of each table. The dynaset shows the combination of the tables. Notice that check no. 2 is not in the dynaset. That’s because the inner join requires a match in both tables. Because there was no vendor D in the Vendors table, check no. 2 to vendor D has no matching record and, therefore, does not appear in the dynaset.

The inner join is the default join property of most data extraction and analysis tools used by auditors, including Microsoft Access, ACL Services Ltd.’s ACL Desktop Edition, and CaseWare International Inc.’s IDEA Data Analysis Software. Modifying the default join property makes it easier to spot irregularities and warning signs of fraud.

The following scenarios illustrate the need for auditors to change the default join property to ensure their analysis includes all transactions, even if there are no matches in the master data:

Allen is a database administrator. He is authorized to manage the company’s ERP software and has been granted update access to the application database. Using his access privileges, he added a shell company to the vendor table. Periodically, he added a record to the table of approved invoices. On the entered due date, the check was prepared and sent to the “vendor’s” (Allen’s) address. When the auditor requested purchasing data, Allen handled the request. He then used his access privileges to remove the vendor record before sending the data to the auditor

Beth works in accounts payable. Although she is responsible for processing checks, a flaw in her application access permissions has also given her the ability to perform vendor maintenance that, within a certain application, grants her update and delete authority to all vendor data. Recognizing the fraud opportunity of this internal control weakness, Beth created several shell companies to which she periodically processed checks sent to the fictitious vendor’s (Beth’s) address. Before the audit team arrived, Beth deleted her shell companies.

LOOK LEFT FOR UNMATCHED TRANSACTIONS
In database terms, an outer join displays all the records of one table and the matching records from a second table. The physical position of the checks and vendors tables in Exhibit 1 follows a common convention used to describe outer joins. Checks, the transaction data table (the primary table), is on the left. Vendors, the master data table (the secondary table), is on the right. Most database software uses this left/right view to help you select the appropriate outer join.

In an Access query, right-clicking on the relationship line displays this Join Properties window:

Selecting the second option, Include ALL records from ‘Checks’ and only those records from ‘Vendors’ where the joined fields are equal, displays every record from the left (Checks) table, even if there is no match in the right (Vendors) table. Using this join property, check no. 2 data now appears in the dynaset:

Another option for auditors conducting analysis is the database access language SQL, or Structured Query Language. The SQL view of the query, below, shows how SQL achieves a left outer join. Examine the statement Checks LEFT JOIN Vendors in the second line. Because the Checks table is left of the JOIN command, all the records from the Checks table appear in the dataset.

A simple filter of the dynaset for missing data in a master file field, such as the vendor name in the previous Access dynaset, will yield a list of unmatched transactions.

ACL adheres to the left/right convention. The Join window identifies its primary table on the left and the secondary table on the right. ACL offers five join properties on the More tab of its Join window. The Include All Primary Records option (see screenshot at right) executes a left outer join, producing the same results as the Access dynaset shown on the previous page.

Alternately, the Unmatched Primary Records option performs a left outer join and filters for records having missing master data. The dynaset would only include the record for check no. 2.

When joining tables using IDEA, fields in the primary table also appear on the left side of its Match Key Fields window:

The All records in primary file option in the Join Databases window executes a left outer join. IDEA’s Records with no secondary match option executes a left outer join and, like ACL, filters the records for missing data, showing only records with no matching data.

LOOK RIGHT FOR UNUSED MASTER DATA
A list of vendors that have received no payments can be a helpful analytical tool for auditors. For example, having no checks written to a county government for property taxes may indicate an unrecorded liability or unreported sale of real estate. A right outer join produces this dynaset.

An Access query using a right outer join is shown below. The third option, Include ALL records from ‘Vendors’ and only those records from ‘Checks’ where the joined fields are equal, will display every record in the Vendors table, even if there is no match in the Checks table.

 

Using this join property, vendors Carlson Co. and Franks, Inc. are listed even though there is no related check data:

 

The SQL view of the query, below, shows how SQL achieves a right outer join. Examine the statement Checks RIGHT JOIN Vendors in the second line. Because the Vendors table is to the right of the JOIN command, all the records from the Vendors table appear in the dynaset.

ACL produces a right outer join with the Include All Secondary Records option on its Join window. A filter for missing transaction data, such as the check field, would be required to produce a list of unmatched master data.

IDEA’s Records with no primary match option executes a right outer join and automatically filters for missing transaction data. The resulting dynaset would only display the records for Carlson Co. and Franks, Inc.

MATCHING MASTER FILES
A classic audit test for detecting a shell company is to match employee addresses with vendor addresses. This test requires two master data tables, a join property and an inner join.

In the master data tables shown in Exhibit 2, it appears that Stephen Matthews may have created two shell companies. The dynaset shown was generated using Access’ default join property. SQL uses the INNER JOIN command (Employees INNER JOIN Vendors) to achieve the same results.

ACL requires a change in its join property to achieve the same results. ACL’s default join property only displays the first match with SM Services Co. To display both matches, the join property must be changed to Many-to-Many Matched Records.

IDEA’s Visual Connector tool yields the same dynaset by matching tables via a user-friendly interface:

 

BEYOND AUDITING
These steps aren’t just for auditors. Any accountant creating a report by joining data tables must consider the possibility that not all transaction data may be matched with master data. Failure to account for discrepancies may result in incomplete reports and misleading information.

Mark W. Lehman, CPA, Ph.D., is an associate professor in the Richard C. Adkerson School of Accountancy at Mississippi State University. His e-mail address is mark.lehman@msstate.edu..

 

Audit Applications

Changing join properties enables auditors to perform a variety of audit tests. What follows is a summary of the required join properties together with examples of potential audit tests:

UNMATCHED TRANSACTIONS
Access: All records from the left table with a filter on a master data field
ACL: Unmatched Primary Records
IDEA: Records with no secondary match
SQL: LEFT JOIN
Employ to search for:

Invoices with no receipts

Transactions charged to accounts that do not exist

Payroll checks issued to false employees

Sales returns to customers that do not exist

Sales returns of inventory items that do not exist

Journal entries entered by ghost employees

UNMATCHED MASTER DATA
Access: All records from the right table with a filter on a transaction data field
ACL: Include All Secondary Records with a filter on a transaction data field
IDEA: Records with no primary match
SQL: RIGHT JOIN
Employ to search for:

Inventory items that have not been sold

Customers that have not made a purchase

Vendors that have not been used

Employees that have not been paid

Invoices that have not been paid

MATCHING MASTER DATA
Access: Default join property
ACL: Many-to-Many Matched Records
IDEA: Relate tables using Visual Connector
SQL: INNER JOIN
Employ to search for:

Employee addresses that match vendor addresses

Employee initials that match vendor name

Employee phone numbers that match vendor phone numbers