Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries > Calculated Numbers
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries: Calculated Numbers

In a query you can create new fields that use values from 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 that are numbers. The general form is the same as for text fields:
 NewFieldName:expression

Examples using numbers:

  • Profit: [Income]-[Expenses]

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

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

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

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

TipCalculated controls: In the next project, Forms and 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.


Access Functions using Numbers

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 create a calculated number field
to format a calculated value as part of the expression
to create a conditional value with IIf statement
to use nested IIf statements

Start with: Class disk, Projects database (projects2-Lastname-Firstname.accdb) from previous lesson open.

Create Calculated Number Field

In the Projects table you have a budget amount and an actual cost, at least for some projects. some projects are apparently not finished yet. You can create a query with a field that calculates the difference.

  1. Icon: Back Up diskBack up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.

    Icon: Access 2007Access 2007: Button: Office > Manage > Back Up Database

    Icon: Access 2010 Access 2010: File > Save and Publish > Back Up Database > Save As button

    Icon: Access 2013 Icon: Access 2016 Access 2013, 2016: File > Save As > Back Up Database > Save As button

  2. Icon: Design View Create a new query based on the Projects table.
  3. Include the fields ProjectID, ProjectName, Cost, and Budget.
  4. Create a new field by typing:
    Compare:[Budget]-[Cost] into the Field cell for a new column.
    Icon: TipTo see your typing better: Use the Zoom window.
    (Right click on the Field cell and select Zoom.)

    Query Design View: Compare (Access 2013)

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

  5. Query Datasheet View: Compare (Access 2013)

    Icon: Run Run the query.

    Icon: TroubleProblem: New column does not show
    You must TAB or click out of the column in Query Design View to finish adding the new field definition.
    Solution: You must create the new field again.

  6. Icon: Datasheet View Drag the bottom edge of row 1 upwards to remove the wasted space.
  7. Inspect the Compare column.
    How many projects went over budget?
    One - the one with values in parentheses. That is Accounting formatting for negative numbers.

    In previous versions of Access the Compare column values did not inherit the Currency data type. It's nice to see that glitches can get fixed.

    Another glitch fixed is in records 6 and 7. Previous versions of Access showed a value of 0 in the Compare column even though there was nothing in the Cost column to use. Leaving the Compare column blank when a value is missing is better.


Format with Expression

Did you notice that all of the values are in even dollars, no cents? That seems to waste space! You can format the values to hide those zeroes.

Access has a special function, Format, for applying a particular format to your calculated or 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 with the standard Currency format, 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 View Switch to the Query Design View.
  2. Query Design View: Compare: Format([Budget]-[Cost],"$##,###")Edit the Compare field to read:
    Compare: Format([Budget]-[Cost],"$##,###")

  3. TAB to the next column.
    The pattern is automatically edited, dropping one of the hash marks to the left of the comma. Unexpected!

    The pattern tells Access to put the dollar sign $ in front, to use a comma to separate thousands, and to show no digits to the right of the decimal point. Any digits to the right of the original decimal place are rounded.

  4. Icon: Run Run the query.
    Icon: Datasheet View Is this better? What is different?
    • Query Datasheet View: Compare, formatted as currency (Access 2013)The Compare column can be resized to use less space.
    • The negative numbers are now shown with a negative sign in front instead of in parentheses.
    • The values are aligned to the left like text.
      Something about the Format function is doing this.
    • The Compare column for ProjectID #2 shows a $ and no numbers instead of $0.00 that it showed before. Is that clearer to you?

    Perhaps we need to make some other changes to make it clearer which projects are actually finished and which are not and also whether or not the project went over its budget.


Create Conditional Value with IIf Statement

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

You can type the name of this function as IIF, IIf, or iif. Access will revise your typing to IIf.

  1. Icon: Design View Switch back to Query Design View.
  2. Add a new field as follows:
    Compare2: IIf([Cost]=0 Or [Cost] Is Null,"Cost not entered",Format([Budget]-[Cost],"$#,###"))
    Be careful to have a closing parenthesis for each opening parenthesis!

    This conditional expression puts warning text in the field if Cost = 0 or is null. It shows the difference between the Budget and Cost otherwise, in the same formatting as before.

  3. Icon: Run Run the query.

  4. Icon: Datasheet View Widen the Compare2 column to show all of the text.

    Query Datasheet View: Compare2: IIf([Cost]=0 Or [Cost] Is Null,"Cost not entered",Format([Budget]-[Cost],"$#,###")) (Access 2013)The explanation is better than a blank value. But we are not done yet.
     


Use Nested IIf Expressions

You can format the values to handle more than two choices by nesting one IIf expression inside another one. That way you can make Access show something different for each of the three alternatives - positive number, negative number, Cost has not been entered yet.

  1. Icon: Design View Switch back to Query Design View.
  2. Edit the expression for Compare2 to read:
    Compare2: IIf([Cost] is null,"Cost not entered",IIf([Budget]-[Cost]<0,"UNDER budget","OVER budget"))

    This expression is a conditional inside a conditional.

    In words: If Cost is empty, show the text "Cost not entered". If there is a value in Cost, look at [Budget]-[Cost]. If that value is less than zero, show the text "UNDER budget". If not, show the text "OVER budget".
    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")) (Access 2013)Icon: Run Run the query.

    There is one spot where expression did not work well. For the My Trips database project, the cost and budget values are the same so the difference is equal to zero. But the Compare2 column shows OVER budget. That's not true! We could nest another IIF statement. We will stop for now.

    It would be even better if you could color code the results so you could scan the datasheet instead of having to read the text in Compare2. Unfortunately, that can not be done in a table or query datasheet. In a later lesson we will learn how to do that for controls in a form or report.

  4. Button: Save (Access 2010) Save the query as QProjects-Compare2 and close it.