Tables & Queries:
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016
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:
Example using numbers:
Calculated 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.
Project 3: Tables & Queries
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.
Start with: , Projects database open.
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.
Enter Values: Character not on keyboard
Modern 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
Character 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.
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.
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
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:
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.
It will take two IIf expressions, one inside the other, to handle the three alternatives - positive number, negative number, Cost = 0.
~~ 1 Cor. 10:31 ...whatever you do, do it all for the glory of God. ~~