The Excel® Analyst's Guide to Access®

Chapter 5. Working with Calculations and Dates

The truth is that few organizations can analyze their raw data at face value. More often than not, some preliminary analysis with calculations and dates must be carried out before a big-picture analysis can be performed. Again, Excel is the preferred platform for working with calculations and dates. However, as you will learn in this chapter, Access provides a wide array of tools and built-in functions that make working with calculations and dates possible.

Using Calculations in Your Analysis

If you are an Excel user trying to familiarize yourself with Access, one of the questions you undoubtedly have is "Where do the formulas go?" In Excel, you have the flexibility to enter a calculation via a formula directly into the dataset you are analyzing. You do not have this ability in Access. So where do you store calculations in Access?

As you have already learned, things work differently in Access. The natural structure of an Access database forces you to keep your data separate from your analysis. In this light, you will not be able to store a calculation (a formula) in your dataset. Now, it is true that you can store the calculated results as hard data, but using tables to store calculated results is problematic for several reasons:

• Stored calculations take up valuable storage space.

• Stored calculations require constant maintenance as the data in your table changes.

• Stored calculations generally tie your data to one analytical path.

Instead of ...