MS Access for the Business Environment: Create a Calculated Field with the Expression Builder

About the Series …

This is the first article of the
series, MS Access for the Business Environment. The primary
focus of this series is an examination of business uses for the MS Access
relational database management system. The series is designed to provide
guidance in the practical application of data and database concepts to meet
specific needs in the business world. While the majority of the procedures I
demonstrate will be undertaken with Access 2002, many of the concepts
that we expose in the series will apply to numerous versions of MS Access.

As we progress through the series, we will build upon
previous lessons and the concepts we have introduced therein, in many cases.
However, one of my objectives is to make each lesson as "standalone"
as possible, in an attempt to prevent cases where one cannot complete a given
lesson without having components or objects that were created in previous
lessons. This should make it easier for "casual" visitors to join us
with any lesson, and still successfully complete that session, given an
existing understanding of concepts and principles that we have accumulated up
to that point.

To get the most out of the MS
Access for the Business Environment
series, you will need to
have installed MS Access 2002, although, again, the majority of the
concepts we take up will certainly apply to earlier versions. Make sure that
the PC that you plan to use meets the system requirements, including
hardware and operating systems, of MS Access, as well.

Introduction to this Tutorial

This tutorial will
introduce calculated fields in MS Access. We can use calculated fields
both in queries and tables; here, we will focus upon their creation and use in
general. This lesson will include:

  • A brief introduction to calculated fields;
  • An introduction to the MS Access Expression Builder;
  • A practical walkthrough of the process of creating a
    calculated field.

Let’s begin by discussing the reasons we might want to
create a calculated field and how such a component can help us to extend the
power of our Access databases.

Introduction to Calculated Fields

Many occasions arise in the design of a database where
calculated fields are useful. The most common scenario surrounds fields that
contain derived data–that is, fields that are made up of data that
already exists elsewhere in the database. An example might be the net price of
a product that an organization sells, after discounts. A table under
consideration might house the retail price and the discount information, but
the net price, or retail price less the discount, is a derived amount. We can
more efficiently meet a business need for the net price by installing a
calculated field that would provide the information as needed.

Using the Expression Builder to
Create Calculated Fields

Let’s open the Northwind sample
database, and begin by creating a rudimentary calculated field. We will use the
Expression Builder, which can often assist us in the creation of
expressions when we are learning about expressions, and perhaps Access, in
general. We could also enter the expressions directly, if we were certain of
the syntax required and how to add it, but for now, we will use the Expression
Builder.

We will start Access and proceed,
taking the following steps:

1.
Go to the Start
button on the PC, and then navigate to the Microsoft Access icon, as
shown in Illustration 1.



Illustration 1: The Microsoft Access Icon from the Start
Menu

2.
Click the icon
to start Access.

Access
opens, and, depending upon whether you have opened it before, may display an
initial dialog. If so, close it.

3.
Select File
-> Open from the top menu, and navigate
to the Northwind sample database (the file might also be accessed from
the Open a File menu atop the task pane, if it has not been disabled
previously, at the right side of the main window in Access 2002.)

4.
Select Northwind.mdb,
closing the splash screen if it appears.

The
main switchboard appears as shown in Illustration 2.



Illustration 2: Inside Access, Northwind Main Switchboard

5.
Click the Display
Database Window
, or get there by an alternative approach.

We
arrive at the Database Window, which appears as depicted in Illustration
3
.



Illustration 3: Inside Access, Northwind Main Switchboard

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles