Want More Effective and Efficient Data Analysis? Use Access!
Samuels, Janet A., Wood, Robert E., Strategic Finance
HAVE YOU EVER NEEDED TO ANALYZE DATA to help answer a question only to discover that your current system isn't designed for the analysis you want to perform? Or have you ever needed to perform an analysis that required data from several systems or that involved combining data from company systems with external sources such as department-maintained spreadsheets or data from surveys or public sources?
Accountants often use Microsoft[reg] Excel to extract and analyze data, but working with data in Excel can be very time-consuming and cumbersome--especially when information needs to be combined from multiple files and then manually checked for data integrity. For example, assume you want to determine if certain types of customers are more likely to generate warranty claims. To do so, you'd want to analyze sales and warranty claims by customer type. Yet customer type isn't included in the company's information system. Instead, your marketing department keeps an Excel worksheet that lists customer type for all customers (e.g., retail, wholesaler, home builder, etc.). In order to perform the analysis, you could use a combination of Excel and manual means to combine the data, check it for errors, and then produce the desired summaries by customer type. Microsoft[reg] Access, however, is a better, moreefficient and effective tool to use.
Importing the data into Access connects information from disparate systems and transforms it into a more usable form, enabling meaningful data analysis and allowing for automatic data validation. There are five steps to performing data analysis with Access: Gather data, create a database, edit and validate data, connect data files, and perform queries. It's important to note that the databases we discuss aren't meant to be comprehensive databases that support an organization's transaction systems, data marts, or data warehouses. Rather, they are temporary databases set up quickly for the sole purpose of data analysis. They can be used once or updated periodically (e.g., for monthly analysis). Using these databases requires a basic familiarity with Access, including an understanding of table, relationship, and query construction. (Table 1 provides a brief review of database terminology used in this article.) This article takes a detailed look at the first four steps of the process, which involve preparing the data for analysis in Access.
Table 1: Common Access Database Terms Table--An Access database needs to contain at least one table, and it often contains many different tables. Tables can be thought of as similar to a worksheet in an Excel workbook. Field--Each table contains one or more fields. A field is a column of data (similar to a column in an Excel worksheet). For example, in a Customers table, there might be a field for customer number, another field for zip code, etc. Fields can be created as text or numeric data types. Primary Key--For each table, one field is typically identified as the primary key (although a compound primary key is possible). A primary key must be a unique identifier and can't be empty or null. For example, in the Customers table, customer name wouldn't be a good primary key because two different customers might have the same name. As long as all customers have a customer number--and each one is unique--then customer number would be a valid primary key. Record--Within each table, there may be one or more records. These are similar to rows in an Excel worksheet. For example, in the Customers table, each customer is a separate record. Relationships--Related fields in separate tables can be connected via a relationship. For example, the customer number field in the Customers table may be connected via a relationship to the customer number field in the Sales table.
Step 1: gather data
The first step is to determine exactly what analyses you want to perform and ensure that you gather all the requisite data. …