In this tutorial, you have worked through several examples of how to perform analyses using ACL. What other analyses could you perform with the data?
Consider these questions. You can likely come up with numerous ideas that are applicable to your own work.
■ Do the transactions have unacceptable Merchant Category Codes?
■ Do the Merchant Category Codes that are used match the cardholder’s responsibilities?
■ Does the cardholder conduct repeated
transactions at regular intervals with the same merchant?
■ Does the cardholder have several transactions within a brief time period (say 72 hours) with the same merchant?
■ Does the cardholder have transactions occurring on weekends or holidays?
■ Do an unusual number of transactions have rounded values, such as $1000, $200, or $450?
■ Are there credits charged back to a card number, and what are the explanations for these credits?
■ Does the cardholder regularly come close to or exceed his or her credit limit?
■ Has there been a sudden increase in the cardholder’s purchases?
■ Is the cardholder responsible for charging other people’s expenses?
■ Which five or ten employees spent the most? To
Chapter review
Is there any relation between spending a great deal and charging unacceptable transactions?
■ Determine the total spending by department on a monthly basis. Use theDeptCode field in the Employees table.
■ If an employee has charged air fare, what other travel-related expenses has he or she charged? For example, if an employee charges air fare, he or she is likely to charge a hotel room and transportation.
Is this the case? What are some possible explanations for the patterns that appear?
■ If the codes for caterers and bakeries were
unacceptable, how much would money have been saved?
■ Chapter review
In this chapter you accomplished the following tasks:
■ Imported an Excel workbook with two worksheets.
■ Combined information from two tables into one table.
■ Corrected a data field that included invalid data.
■ Extracted the new transactions table to Excel.
■ Corrected a table by filtering out an invalid record and extracting the results to a new table.
■ Created a table that combines all of the CC transactions into one table.
I NDEX
A
Absolute value
Profile command results12 Acceptable_Codes.mdb
data file5
Access. See Microsoft Access data Account number categories
exporting to other35 Ascending
Quick Sort order15 ASCII
data type27 ASCII data
formatting in expressions36 Audit trail
removing leading and trailing45,70 Bonuses
finding total15
C
Calculated fields. See Computed fields Categories
data type28
expense, Metaphor Corporation4 Cells
blank55 Character data
applications27
formatting in expressions36 numerals in24 and field names25 Column width
changing in view 17 Columns
adding to view52,54,58,72 changing sort order in view10,15 removing from view55
Combining data Duplicates13,69 Export35,73 Extract56,58 Join50 Profile12
Relations50,54,58,60 running on single fields11 selecting fields to include13 Statistics11
Summarize42 Total15,34
importing25
Company_Departments.txt data file5
Computed fields adding to view57 creating17,52,54
finding expiry date34 Credit_Cards_Metaphor.xls
importing through the wizard22 integrity testing13,27
samples for testing7 sources6
validity errors, finding29 verifying type29
viewing in original format6 Data Definition Wizard
changing24,35 checking51,53,66 verifying27 Data type categories
Table Layout window28 Data-entry errors
finding69 Date data
Statistics command11 Dates
formatting in expressions16 selecting in Expression Builder34 Decimals
Quick Sort order15 Digits
using as character data24
Documentation
Edit Field Properties screen using24 End of file record deleting73 data-entry7,69 testing for65 validity68
Excel. See Microsoft Excel data Expenses
allowable
Metaphor Corporation4
Index
Expiry dates finding34 Export
using Excel format73 Export command
using35,73 Expression
defined16 Expression Builder
See also Edit view filter functions list35 opening52 using17 Expressions
checking syntax36 creating and saving17 formatting
character data36 multiple conditions in38 Extract command
and column titles25 importing from data file24 Field order
changing in the view10 selecting for export73 Field separators
specifying24 Field totals
Field width
adjusting in view17 Fields
adding to view52,54,58 blank entries in55 changing order60
combining from multiple tables50 computed28,52,54
editing28
eliminating invalid characters70 harmonizing51,53,59
key51,66
ordering sequentially67 removing from view55 sort order15,37 removing13,17 saving37
setting multiple conditions38 FIND( ) function
using as field names24 Headings
and column order10 and sort order15 History
Excel worksheets23,65 Microsoft Access data25 using the wizard22 INCLUDE( ) function
compare with Relations50 using52
viewing command entries15
M
Profile command results12 MDB files
importing25
Merchant Category Code list about5
data, importing23,65 export format73 Minimum value
Profile command results12 Multiple tables
alpha characters in29 Profile command11,12 Statistics command11
O
Objectives tutorial2 Operators
using in filters38,45 OR statement
MATCH( ) function45 Order
ascending or descending15,37 columns in the view10
Order of fields
arranging in view67 Output fields
Index Properties of ACL project
displaying11
Record layout. See Data, format information Record of commands
compare with Join50 using54,58,60 Save the Open Project
button26
SUBSTR( ). See SUBSTRING( ) function SUBSTRING( ) function changing data type35
checking data characteristics27 displaying17
Relations dialog box55 combining50
Company_Dept57 creating23 Employees57 empty53 exporting35,73
methods of combining67 moving to new folder64 opening11
relating54,58,60
removing blank entries55 saving26
Unacceptable_Codes23 Tabs
displaying multiple results66 Task sheet
sample30 Test data
using to refine strategy7 Text
Profile command results12 Totals
accumulating per value46 Trans_April worksheet
importing23 Trans_April.xls
data file5 Transaction totals
per credit card46 Transactions
comparing54
identifying unacceptable53 isolating by category43 split69
summarizing by code42
Type of data
Use first row as field names option24
V
Validate
compare with verify70 Validity errors
checking65 Values
comparing57
finding in multiple tables50 Verify
compare with validate70 Verify button column sort order15
field order
adjusting in view17 Window
Table Layout17,35 Worksheets
importing multiple65 selecting for import23