University Of Bahrain

College of Information Technology

Tutorial Objectives
By the end of this tutorial you should;
- Be able to join and relate tables based on key fields
- Be able to further analyze your data to deduce decisions and conclusions
-
Exercise 1 – Further Analysis using ACL
In this tutorial, because we are working with data from Metaphor Corporation, we will assume the typical
scenarios that may occur in real life to find results and deduce decisions and conclusions.
Exercise 1.1 – JOIN Tables
Suppose Metaphor Corporation would like to determine the unacceptable transactions that have been
made. Acceptable_Codes and Unacceptable_Codes tables list the codes that are allowed and disallowed by
Metaphor Corp. Codes not listed in any of these are conditional codes. Before doing so, we need to further verify
the data in these tables by ensuring that no common codes are found in each.
JOIN Command – joins data from two different tables based on a key field(s) and creates a new table
with the result.
Exercise 1.1.1 – Creating Computed Fields
Check the fields of CODES through table layout, in Acceptable_Codes and Unacceptable_Codes, note
the differences.
1. Open Acceptable_Codes table -> open Table Layout -> click Add a New Expression and name it
Codes_Sub_Acceptable -> in the expression builder, enter the following:
SUBSTR (ALLTRIM (CODES, 1, 4)) and ensure the length is 4.
Save your work -> verify and close Table Layout -> add the new field to the View.
2. Close this table, and repeat the previous step with Unacceptable_Codes table, name the new field:
Codes_Sub_Unacceptable.
Exercise 1.1.2 – Using “JOIN”
Now that we have prepared the tables with key fields, we can join them. We are joining them to see if
any codes exist in both.
1. Open Unacceptable_Codes table -> select Data -> Join Tables
2. Select Acceptable_Codes as the secondary table.
Select Codes_Sub_Unacceptable and Codes_Sub_Acceptables as primary and secondary key,
respectively.
Select Codes_Sub_Unacceptable and Description as primary fields.
Select Presort Secondary Table.
In the TO field, name the table Codes_in_both_tables
3. Go to the MORE tab ->select Matched Primary Records and ensure that no other Join Categories
are selected -> click OK.
4. Observe the new table create and count the records.

Because there are no records in the Codes_in_both_tables table, you know that no cades exist in
both the Unacceptable_Codes and Acceptacle_Codes Tables. The original code tables have no
obvious errors, so you can use them for your analysis.

1
Prepared By: Ahmed Ramadan
 University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Exercise 1.2 – Relate Tables
Continuing with the scenario from the previous exercise, now that we have ensured our data is correct;
now we will relate tables that have common fields to retrieve the data that we need.
RELATE Command – just like join, but does not physically join tables; create a virtual join between 2 or
more tables. This allows us to use related fields as if they were from the original table.
Exercise 1.2.1 – Using “RELATE”
Before we begin to relate the tables Trans_April and Unacceptable_Codes, we need to ensure that
they have common fields. Add a computed field in Trans_April to clean the CODES, name it: Codes_Sub_April.
Now we will isolate all transactions that have similar codes as the codes in Unacceptable_Codes.
1. With Trans_April table active -> select Data -> Relate Tables.
2. Select Add Table and choose Unacceptable_Codes ->
Click and drag Codes_Sub_April from Trans_April to Codes_Sub_Unacceptable at
Unacceptable_Codes -> click Finish and add Codes_Sub_Unacceptable to the View.
3. Now apply a filter to display only transaction made to unacceptable categories, right-click any blank
entry in Codes_Sub_Unacceptable -> select Quick Filter -> Not Equal -> remove
Codes_Sub_April and count the records. -> extract these records to a new table called
Trans_April_Unacceptable
Exercise 1.3 –
With the results from the previous exercise, we can now continue with the problem at hand and identify
the employees who have conducted these transactions. The goal of this exercise is to create a view with the
following information (Employee Name, CCN, Department, Transaction Amount, Transaction Date,
Transaction Code, Transaction Code Description).
Exercise 1.3.1 – Link Employees to their Company Departments
1. Using the techniques you learned in the previous exercises, create a computed field for
Dept_Code_1 in Company_Dept and Emp_No_1 in Employees both with length=1.
2. Relate the tables on the newly created computed field with Employees table as the parent ->
then add Dept_Name to the Employees View.
3. Extract the following fields (First and Last Name, Card Number, and Department Name) to a
new table called: Employees_Dept.
Exercise 1.3.2 – Getting a Detailed View
1. Create a computed field for CardNum in Trans_April_Unacceptable called CardNumTrim with
length=16.
2. Relate the tables Trans_April_Unacceptable and Employees_Dept with the key field as Card
Number.
3. With all three tables now related, we can edit the columns to display only the fields we need as
mentioned in the scenario – arrange them as follows: (First Name, Last Name, Dept_Name,
Amount, Description, Trans_Date, Card Number, Codes Sub Unacceptable, and Customer Number).
Exercise 1.3.3 – Report Findings
With the results finally achieved, we can provide Metaphor Corp. with a report of all employees that have
conducted unacceptable transaction, and they can further investigate the reason behinds such transactions.
1. Select Data -> Report -> set the header: Unacceptable Transactions at Metaphor Corp. in April
2003 -> set the output to Screen -> click OK.
2. Select Data -> Report -> set the output to File as a HTML Text File and name it
Unacceptable_Trans_Apr_2003 -> click OK.
2
Prepared By: Ahmed Ramadan