Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jun 5, 2003

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

By William Pearson

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

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM