O'Reilly logo

The Excel® Analyst's Guide to Access® by Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required