Creating a Calculated Field with Access 2007

A calculated field takes information from another field in your Microsoft Access 2007 database and performs some arithmetic to come up with new information. In fact, a calculated field can take data from more than one field and combine information to create an entirely new field if that's what you want. You can perform simple arithmetic, like addition and multiplication, or use Access's built-in functions, such as Sum and Avg (average), for more difficult calculations.

The first step when creating a calculated field (also known as an expression by Access) in a query is to include the tables that contain the fields you need for your calculation. Access can't pull the numbers out of thin air for the calculation, so you must make sure the fields that contain the numbers are present in your query.

Access uses a special syntax for building calculated fields. Here's how to create a calculated field:

1. Click an empty column in the Field row of the query grid.

The good old cursor will blink in the row. Access puts the results of the calculation in the same grid position as the calculation itself, so if the calculation sits in the third column of your query grid, the calculation's results will be in the third column, too.

2. Enter a name for your calculation followed by a colon (:).

Access will refer to this calculation from now on by whatever you enter before the colon. Keep it short and sweet, like Amount or Tax, so it's easier to refer to later on. If you don't name your calculation, Access will put the generic Expr (followed by a number) as its name. It has to be called something, so why not Expr1 or Expr2, right?

3. Enter your calculation, substituting field names for the actual numbers where necessary.

You don't have to exclusively use field names in your calculations. You can also enter formulas with numbers, like this:

Tax: Quantity * UnitPrice * .06

If a field name contains more than one word, put square brackets around it. Access treats anything else it finds in the calculation as a constant (which is math lingo for it is what it is and it never changes). If the field name contains no spaces, Access will put the square brackets in for you after you enter the field name. That's why you should always use one-word field names — so you don't have to type those darned square brackets.

When creating formulas, keep these general guidelines in mind:

• You must manually type the field names and constants into your formula. You can't just drag and drop stuff from the table list.

• Don't worry if your calculation grows past the edge of the Field box. Access still remembers everything, even if it doesn't appear on the screen.

To make the query column wider, aim the mouse pointer at the line on the right side of the thin bar above the calculation entry. When you get it right over the line, the pointer changes into a line with a horizontal arrow through it. When that happens, click and drag the mouse to the right. As you do, the column expands according to your movements. To fit the width to just the right size, position the mouse to size the column as described above then double-click!

• If it's a really, really, really long calculation, press Shift+F2 while the cursor is somewhere on the calculation.

This opens the Zoom dialog box so that you can easily see and edit everything in a pop-up window.

When you run a query containing a calculation, Access

• Produces a datasheet showing the fields you specified

• Adds a new column for each calculated field

dummies

Source:http://www.dummies.com/how-to/content/creating-a-calculated-field-with-access-2007.html