Want More Effective and Efficient Data Analysis? Use Access!

By Samuels, Janet A.; Wood, Robert E. | Strategic Finance, October 2007 | Go to article overview

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. …

The rest of this article is only available to active members of Questia

Already a member? Log in now.

Notes for this article

Add a new note
If you are trying to select text to create highlights or citations, remember that you must now click or tap on the first word, and then click or tap on the last word.
One moment ...
Default project is now your active project.
Project items

Items saved from this article

This article has been saved
Highlights (0)
Some of your highlights are legacy items.

Highlights saved before July 30, 2012 will not be displayed on their respective source pages.

You can easily re-create the highlights by opening the book page or article, selecting the text, and clicking “Highlight.”

Citations (0)
Some of your citations are legacy items.

Any citation created before July 30, 2012 will labeled as a “Cited page.” New citations will be saved as cited passages, pages or articles.

We also added the ability to view new citations from your projects or the book or article where you created them.

Notes (0)
Bookmarks (0)

You have no saved items from this article

Project items include:
  • Saved book/article
  • Highlights
  • Quotes/citations
  • Notes
  • Bookmarks
Notes
Cite this article

Cited article

Style
Citations are available only to our active members.
Buy instant access to cite pages or passages in MLA, APA and Chicago citation styles.

(Einhorn, 1992, p. 25)

(Einhorn 25)

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

Cited article

Want More Effective and Efficient Data Analysis? Use Access!
Settings

Settings

Typeface
Text size Smaller Larger Reset View mode
Search within

Search within this article

Look up

Look up a word

  • Dictionary
  • Thesaurus
Please submit a word or phrase above.
Print this page

Print this page

Why can't I print more than one page at a time?

Help
Full screen

matching results for page

    Questia reader help

    How to highlight and cite specific passages

    1. Click or tap the first word you want to select.
    2. Click or tap the last word you want to select, and you’ll see everything in between get selected.
    3. You’ll then get a menu of options like creating a highlight or a citation from that passage of text.

    OK, got it!

    Cited passage

    Style
    Citations are available only to our active members.
    Buy instant access to cite pages or passages in MLA, APA and Chicago citation styles.

    "Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn, 1992, p. 25).

    "Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn 25)

    "Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences."1

    1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

    Cited passage

    Thanks for trying Questia!

    Please continue trying out our research tools, but please note, full functionality is available only to our active members.

    Your work will be lost once you leave this Web page.

    Buy instant access to save your work.

    Already a member? Log in now.

    Oops!

    An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.