Tables & Queries:
Calculated Values - Numbers

Title: Jan's Illustrated Computer Literacy 101


In a query you can create new fields that use values in other fields to do calculations and comparisons. The new field is not added to the table design, but it can be used in a form or report.

Calculating Values with Numbers

It is easy to write your own expressions to calculate a new field in your query using other field values. The general form is:
 NewFieldName:expression

Example using numbers:

  • Profit: [Income]-[Expenses]

  • TotalPrice: [Price]+[SalesTax]-[Discount]

  • Score: (([Points]+[BonusPoints])/[MaxScore])*100

  • UnitScore: MAX{([Pretest]),(([Exam]*0.75)+([Assignment]*0.25))}

  • DiscountPercent: IIf([OrderTotal]>100, 25,0)

  • Tax: ([Price]-[Discount])*0.0975

TipCalculated controls: In the next project, Forms & Reports, you will learn how to use similar expressions to create calculated controls for a form or report. However, you name the control in a different way and the expression must start with the equal sign (=) instead of the new field name and a colon.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    Summary
    Quiz
    ExercisesTo subtopics

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



Access Functions

Access has a large number of built-in functions, including standard math and financial functions, plus a number of functions that are special to Access. Quite a number of these special functions are for handling dates and times. Dates/Times are so messy to calculate with that the next lesson will focus on those functions.

Examples:

  • Sqr([Length]) returns the square root of the value in the field Length

  • Round([FinalValue],3) rounds the amount in the field FinalValue to show 3 digits to the right of the decimal

  • Now() returns the current date and time

  • DatePart ("yyyy", [EndDate]) returns the year part of the date in the field EndDate.

  • Format(Now(),"mmm") returns the current month as a three character abbreviation.


Icon: Step-by-Step 

Step-by-Step: Calculated Values - Numbers

 Icon: Step-by-Step

What you will learn:

to add currency field
to enter character not on keyboard
to create a calculated number field
to format a calculated value
to calculate a value with IIf
to use nested IIf statements

Start with:  Class disk, Projects database open.

Add Fields

The tables you have in the Projects database so far do not have much in the way of number data to do calculations with. You need to add some number fields and data.

  1. Table Design View: Projects, new field Cost with Currency formatOpen the table Projects in Table Design View.
     
  2. Add a new field to the list named Cost.
     
  3. Select the data type Number and change the Format property to Currency.
     
    The currency formatting will match the default currency from the Regional and Language Options dialog in the Control Panel. If you choose English - American, you see $ as the currency symbol and a period before the decimal part of a number. If you choose Spanish - Paraguay, you see Gs as the currency symbol and a comma before the decimal portion of the value. You must know what country you are in!

    The default value for a number field is zero. You won't have a null value for this type of field.

     
  4. Add another field named Budget with the same characteristics.
     
  5. Icon: Save Save the table and switch to Table Datasheet View.
     

Enter Values: Character not on keyboard

Symbol fontModern fonts have a LOT more characters than are on the keyboard. Many languages use characters that are not in English. For example Spanish includes many accented characters like á, é, ú, Õ and ñ. French includes characters like è and ç. You can buy a keyboard that is customized for your own language.

Characters that are not on your keyboard can be entered with a combo of ALT + keypad numbers. But, how do you know what the number is for your special character??

Symbol Font: In Word and Excel you have a menu command,  Insert | Symbol..., that lets you see all of the characters in a font and use a handy Insert button. The bottom of the Symbol window shows the number to use with the ALT key. Unhappily, Access does not have this command.
 

Character MapCharacter Map: You can open another feature, Start menu | Run | type charmap into the box and click OK. The Character Map window opens. Click on a character and its key combo shows at the bottom right. The illustration is from WinXP Pro.

Alternate keyboard layout: If you work a lot in a second language, you can install an alternate keyboard layout in Regional and Language Options in the Control Panel, and then switch back and forth between layouts. Of course, your physical keyboard won't change so you must be familiar with what characters are where.

Or you can make a cheat sheet of the numbers like I did for Spanish characters to keep handy when I am editing the Spanish version of one of my lessons.


 

  1. Table Datasheet View: ProjectsEnter the following values into the Cost and Budget fields:

    Project 1:    433     450
    Project 2:  1500    1500
    Project 3:  2348    2000
    The table automatically formats these as currency.
     

  2. Add the following additional records:
     
    ProjectName ProjectDescription Cost Budget
    Late Winter Sale Promoting travel to warm climates at end of winter season $225.00 $250.00
    Recruitment for Australia Campaign to hire agents and other staff for the new Australia regional office $5,125.00 $5,000.00
    BMA Medical Conference Meeting planning/setup for medical conference in São Paulo, Brazil   $7,500.00
    Networking Upgrade Researching choices for systems and vendors for a new network   $100.00

    Icon: TroubleProblem: How to type characters not on the keyboard
    To type in the medical conference record (for São Paulo), hold the ALT key down and on the numeric keypad press 0227.  Now you know one code!

    Table Datasheet View: Projects - with new records

  3. Close the table.


Create Calculated Number Value

Now that you have a budget amount and an actual cost, at least for some projects, you can create a query with a field that calculates the difference.

  1. Create a new query based on the Projects table.
     
  2. Include the fields ProjectID, ProjectName, Cost, and Budget.
     
  3. Create a new field by typing:
    Compare:[Budget]-[Cost] into the Field cell for a new column.
    This express will produce a positive number when the project's Cost is less than the budgeted amount and a negative number when the project went over budget.

    Query Design View: Compare

  4. Icon: Run Run the query.Query Datasheet View: Compare
    Two projects went over budget.
     
    Interestingly, the Compare values are not in Currency format, even though both values used in the calculation were. You can fix that!
     

Format

Access has a special function for applying a particular format to your calculated values or to change the formatting of the original values.

Format: Pattern for display
The Format function allows you to choose a standard pattern or create your own pattern for how to display a value.

Syntax for Format function: Format(value, pattern) - takes the value and formats it like the pattern, which can be the name of a standard format or else a custom pattern.

Examples:
Format([Amount],"Currency") takes a value like 87.6 and formats it as $87.60.
Format([Profit]/[Cost],"Percent") takes a value like 0.06457 and formats it as 6.46%
Format([VersionNumber],"###-##-##.###") takes a value like 23456.6789 and formats it as 2-34-56.679

  1. Icon: Design Switch to the Query Design View.
     
  2. Query Design View: Compare: Format([Budget]-[Cost],"Currency")Edit the Compare field to read:
    Compare: Format([Budget]-[Cost],"Currency")
     
  3. Icon: Run Run the query.
    Query Datasheet View: Compare, formatted as currencyBetter. The negative numbers are now shown in accounting format, in parentheses.
     
    The values are aligned to the left like text. Wonder why that is?
    And another question... The last two projects look like they are under budget but the $0.00 cost is just the default value. This could be confusing! You need a way to tell when this occurs.
     

Conditional Value, IIf

You will create a conditional expression using the IIf function for a new field that will have a value that depends on whether or not the Cost is zero. You used the IIf function in the previous lesson.

Syntax for IIf function:
The general form of an IIf statement has 3 parts.
    IIf(expression, truepart, falsepart)

 where  expression  contains the criteria that you want to evaluate. It must be either True or False.

              truepart  is what you want to see when the expression is True.

              falsepart  is what you want to see when the expression is False.

  1. Icon: Design Switch back to Query Design View.
     
  2. Add a new field as follows:
    Compare2: IIf([Cost]=0,"Cost not entered",[Budget]-[Cost])
    This conditional expression puts a text string in the field if Cost = 0 and shows the difference between the Budget and Cost otherwise.
     
  3. Query Datasheet View: Compare2: IIf([Cost]=0,"Cost not entered",[Budget]-[Cost])Icon: Run Run the query.
     
    Since one alternative is text, the whole field is treated as text. Again, the number values are not in Currency format.  Perhaps we should create a different IIf to have all of the Compare2 values be actual text.
     

Nested IIf

It will take two IIf expressions, one inside the other, to handle the three alternatives - positive number, negative number, Cost = 0.

  1. Icon: Design Switch back to Query Design View.
     
  2. Edit the expression for Compare2 to read:
    Compare2: IIf([Cost]=0,"Cost = 0",IIf([Budget]-[Cost]<0,"Under","Over"))
    This expression is a conditional inside a conditional.
    Here is the logic:  If Cost is zero, show the text "Cost = 0". If Cost is not zero, look at [Budget]-[Cost]. If that value is less than zero, show the text "Under". If not, show the text "Over".
    This is the method for handling multiple options - nested IIf expressions.
     
  3. Query Datasheet View: Compare2: IIf([Cost]=0,"Cost = 0",IIf([Budget]-[Cost]<0,"Under","Over"))Icon: Run Run the query.
     
  4. Icon: Save Save the query as QProjects-Compare2 and close it.