Ch 5 -- Calculations
Microsoft® Access 97 Quick Reference
- 5 -
Calculations
You can use calculations and expressions throughout Access in all objects--tables,
queries, forms, reports, and even macros and modules.
Calculations can help you find all sorts of statistics about your data--from averages,
sums, and maximums, to standard deviations and present value. You can even combine
text fields together in one field. You can type calculations directly or use
the Expression Builder to help you.
In a query, calculations can be done by creating a new field based on the values
of other fields in the query grid. You can also create grouping types of queries
with crosstab and total queries.
In forms and reports, you generally add text boxes for calculations. These
text boxes, as well as everything else on the form or report, are called controls,
You learn more about forms and reports in the next part of this book, "Forms
and Reports."
Blanks and Nulls in Calculations
When you run a query, form, or report with calculations, you might see a blank
when you expect a value. This happens when you have a null value that is part
of any expression; then, the result of the expression evaluates to null. Criteria
for nulls are discussed in "Criteria: Blanks, Nulls, and Empty" in the
Queries and Filters part of this book.
Steps
- 1. If the expression is in a form or report, open in Design View.
Double-click the control containing the expression to open the Properties sheet.
Click in the Control Source property (on the Data tab).
- If the expression is in a query, open the query in Design View and click in the
field with the expression.
- 2. If necessary, press Shift+F2 to zoom and see the whole expression.
- 3. If your expression evaluates to a numeric value and one of your
fields is null, the whole expression evaluates to null. You can use
the NZ function to convert any field to another number. Click before
the field name and type NZ. Enclose the field name in parentheses. For addition
and subtraction, use NZ(Fieldname) to convert null values to zero.
For multiplication and division, use NZ(Fieldname,1) to convert
null values to 1.
- 4. If your expression includes any text strings, use the & operator
instead of + (or the entire expression will evaluate to null when any value is null).
For example, type [Field1]&" "&[Field2].
- 5. If you don't want extra spaces in text strings, you might need to use
Is Null. The IsNull function tests if a value is null. For example, to avoid
having two spaces if someone does not have a middle name, use the following expression:
Name: [Firstname] & " " & IIf(IsNull([Middlename]),"",[Middlename]
& " ") & [Lastname]
- 6. When finished, display and then save the form, report, or query.
NOTE: In a Totals Query, Access does not
include null values for aggregate functions such as Sum, Count, and Average.
If you want to count all records including nulls, use Count(*).
Calculated Fields: Forms and Reports--Create by Typing
You can enter a calculation in a form by typing it or using the Expression
Builder. When you enter a field name that includes spaces as part of the expression,
you must include square brackets around the field name. Access will automatically
put brackets around field names with no spaces.
Steps
- 1. In Design View of a form or report, click the Text Box
tool and then click the form or report and type an equal sign (=).
- 2. If you are including a function, include the function name with
parentheses (arguments might or might not go inside parentheses).
- 3. If the next part of the expression is a field, include field in square
brackets, as in [Fieldname].
- 4. If you are doing arithmetic operations, include + - * /, or if you
are combining text, include the ampersand (&).
- 5. When finished, click outside the text box.
NOTE: Only controls that have Control
Source properties can take an expression with a calculated result. Expressions can
be any valid Access function or operator, values, fields, or identifiers,
and must start with an equal sign.
Calculated Fields: Forms and Reports--Edit Expression
After you type an expression in a text box, you can click the text
box and try to edit the expression. This is okay if the expression is short, but
if the expression is long you will not be able to see the entire formula.
Steps
- 1. Open the form or report in Design View.
- 2. If the Property sheet is not displayed, right-click the text
box, choose Properties, and click the Data tab.
- 3. Click the Control Source property and edit the expression or press
Shift+F2 to zoom. When done with the zoom window, choose OK.
- 4. Save and close the form or report.
Calculated Fields: Queries--Create by Typing
Because you do not want to store unnecessary data in a table, you often
need to do calculations in a query. To create a calculated field in
a query, you use a Field cell in the Query Design grid. Calculated fields
in a query are not stored to disk, but are recalculated at the query's runtime.
Steps
- 1. Open the query in Design view and click an unused Field cell.
- 2. Type the name for the field followed by a colon (:).
- 3. If the next part of the expression is a field, include field
in square brackets, as in [Fieldname].
- 4. If you are doing arithmetic operations, include + - * /, or if you
are combining text, include &.
- 5. Repeat field names and operators as much as necessary, and then click
outside of the field box. If there is a syntax error, Access will give you
a warning message.
NOTE: To edit the expression, click the Field
cell. If necessary, press Shift+F2 to zoom to a larger window.
Calculations: Average of Values
The AVG function returns the average (mean) of the values in a
number field. Unlike Excel's Average function, you have only one expression (which
is a fieldname) in the parentheses rather than multiple cells.
Steps
- 1. In the Database window, click the table or query on which you
want to base the new query and choose Query from the New Object button on
the toolbar. Double click any fields in the Field List you will use for the query.
- 2. To find an average of one value for an entire table, create
a query with just that field. Click the Totals button on the toolbar and choose
Avg in the Total row.
- 3. To find an average of one value for a selected set of records, create
a select query and choose criteria for which values you want to select. Include the
number field in the Query Design grid. Uncheck the show box for all fields
except the number field. Click the Totals button on the toolbar and choose Avg in
the Total row.
- 4. To find the average for a group of values, create a query and put the
field(s) you will group in a column and the numeric field to average in another
column. Click the Totals button on the toolbar and choose Avg in the Total row of
the numeric field. The grouping field(s) has Group By in the Total row. This query
will not show the detail for each record, just the averages for the groups.
- 5. Save the query and click the Run button on the toolbar.
NOTE: To show the detail and the average of these
groups in a report, create a report that is grouped on a field. In the report's
group footer, add a text box and type =Avg([Fieldname]) where
Fieldname is the numeric field to average.
Calculations: Count of Items in List
The easiest way to see a count of all records (or selected records) in a table
is to look at the navigation buttons at the bottom of a table or Query window
after the word of. You can also look at this number for filtered records--more
specifically, you can find a count of the numbers of each item in a group.
Steps
- 1. From the Query tab of the Database window, choose New
and choose Design View.
- 2. Click the Totals button on the toolbar.
- 3. Double-click the field you want to group by (the Total row defaults
to Group By).
- 4. Double-click a field where every record has a value (the primary
key is a good choice). Choose Count in the Total row.
- 5. Run the query, save, and close.
Calculations: Date
Date fields are common in databases. With these fields, you can
find today's date (or a date relative to today's date such as 60 days after). You
can also find the difference between two dates, or the year or month portion of the
date.
Steps
- 1. If the expression is in a form or report, open
in Design View. Double-click the control containing the expression
to open the Properties sheet. Click in the Control Source property (on the
Data tab).
- 2. If the expression is in a query, open the query in Design View
and click in the field with the expression.
- 3. If the expression is for a field in a table, open the
table in Design View. Click in the field you want to change. In the lower half of
the window go to the Default Value or Validation Rule property.
- 4. If the expression involves more than one field in a table, open the
table in Design View. Right-click the title bar of the table and choose Properties.
Click in the Validation Rule box.
- 5. Type one of the expressions listed in the following section. When finished,
save and display the table, query, form, or report.
Using Date and Time Expressions
You can create both simple and complex expressions for use in tables, forms, queries,
and reports. An expression takes a set of values, symbols, operators, or identifiers
and evaluates it to produce a result. The following list illustrates how and when
to use date and time expressions.
- Date() You enter today's date as Date(). This is often the Default
property in the Table Design grid or Form Design property
of a control. This also is combined with an operator such as <Date()
as the Validation Rule property expression or in the criteria cell
of the Query Design grid. In the Field cell of Query Design grid, Date()
is often combined with other date fields as in DaysLate: Date()-[DueDate].
Use Now() if you need to include the current time.
- DateDiff(interval, date1, date2) If you want to find the difference
between two dates in weeks or months, use DateDiff(interval, date1, date2).
Enclose the interval argument in quotes such as "ww" or "m".
See the upcoming note for options on the interval. You would generally use this function
in a Field cell of the Query Design grid or in the Control Source property of a calculated
control on a form or report.
- DateAdd(interval, number, date) If you want to find a date in the
future, use DateAdd(interval, number, date) in the same places
and with the same values for interval as DateDiff. Number is the number
of intervals.
- DatePart(interval,date) To convert a date field to the month or
year portion of the date (or any other interval below), use DatePart(interval,date).
When you use this function, it is often in the Field cell of Query Design grid of
Total and Crosstab queries.
- Format(expression,fmt) If DatePart doesn't give you enough
options, you can also use Format(expression, fmt) where expression
can be a date field and fmt can be the same as interval below with additional choices
(y = year; yy=year with 2 digits--97 or 01; yyyy = four-digit
year; m = month number; mm = month number with leading 0; mmm
= month abbreviation; mmmm = full month name). You enclose the fmt
value in quotes also. Format is often used in Total and Crosstab queries.
NOTE: The interval options for the DateDiff,
DateAdd, and DatePart functions include yyyy (Year), q
(Quarter), m (Month), y (Day of year), d (Day), w
(Weekday), ww (Week), h (Hour), n (Minute), and s
(Second). For formatting numbers, see also "Query: Format Field" in the
Queries and Filters part of this book, and "Format: Numbers and Dates"
in the Forms and Reports part of the book.
Calculations: First and Last Values
When you want to see the first or last value in a list, you can use the First
and Last functions. You need to sort the list or the results of these functions
will be arbitrary.
Steps
- 1. On the Query tab of the Database window, choose the New
button and double-click Design View.
- 2. In Query Design view, click the Totals button.
- 3. Double-click the field to sort and choose Group By in
the Total row.
- 4. Double-click the field to find the order, and choose First or Last
in the Total row.
- 5. View, save, and close the query.
NOTE: You can also see First and Last
values in a report. Create a report that is grouped by one field. In the Group
Footer section, create a calculated control and use the First or
Last functions.
Calculations: Maximum and Minimum
The Maximum and Minimum functions help you find the highest and lowest values
of a field. You can also find these values by sorting a table on the
field of interest Descending or Ascending and looking at the first record.
Unlike the Avg or Sum functions, the field you are finding the
maximum or minimum value of can be a text or date field in addition to a number data
type. The following items show just the maximum (MAX) function.
You can use these identically with the minimum (MIN) function.
Steps
- 1. From the Query tab of the Database window, choose New
and choose Design View.
- 2. Click the Totals button on the toolbar.
- 3. Double-click the field(s) you want to include in your query.
To find the maximum of one value for an entire table, create a query with
just that field. Choose Max in the Total row.
- 4. To find the maximum of one value for a selected set of records, choose
criteria for the values that you want to select. Include the evaluation field
in the Query Design grid. Uncheck the show box for all fields except the evaluation
field. In the evaluation field, choose Max in the Total row.
- 5. To find the maximum for a group of values, insert the field(s) you
will group in a column and the evaluation field to maximize in another column
Choose Max in the Total row of the numeric field. The grouping field(s) has Group
By in the Total row. This query will not show the detail for each record.
- 6. When finished, display and then save the query.
NOTE: To show the detail and the maximum of these
groups in a report, create a report that is grouped on a field. In the report's
group footer, add a text box and type =Max([Fieldname]) where
Fieldname is the numeric field to maximize.
Calculations: Percent
Percent means per 100. When you look for percentages, you are often dividing
a field's value by the total of all values for that field. Normally when you
divide, the result is first in decimal notation (for example, 0.345). When you format
the number as percent, Access multiplies the number by 100 and displays a percent
sign (34.5%). You can calculate a percent of the whole in a report or form.
Another use of percent is to take a percentage of a number. To do the calculation,
you multiply the percent times the number (10% * 500 = 50). A common mistake is to
assume that a field formatted as percent will not need the decimal point or percentage
sign when you enter the number (10 is entered incorrectly instead of .10 or 10%).
You can use a query, form, or report to multiply a percent. (See "Calculated
Fields: Queries--Create by Typing.")
NOTE: To increase an amount by a percentage,
don't forget to include the amount itself by including a "1" in the multiplier.
For example, to increase prices by 5 percent, you would type [Price]*1.05.
Unlike Excel, you cannot type 105% in the formula, and you must use the decimal
equivalent of the number.
Steps
- 1. Create a text box in Form or Report view, right-click
the text box, and move to the Control Source property.
- 2. Type =[Fieldname]/Sum([Fieldname]) where Fieldname
is the value for which you want to find the percent.
- 3. Move to the Format property. Choose Percent from the drop-down
list of choices. Type the number of decimal places you want in the Decimal Places
property.
- 4. View, save, and close the form or report.
Calculations: Standard Deviation and Variance
Standard deviation (STDEV) and variance (VAR) give you an idea
how much your data varies throughout the whole table. If you have a small
variance or standard deviation compared to the absolute value of the number, then
most of your values are close to the mean. If you have a large standard deviation
or variance, then the value of your field is spread across many numbers. Both
functions work the same way ; STDEV is used in the steps of the following
task. For variance, replace STDEV with VAR. Both of these functions
must be used with number (or currency) fields.
Steps
- 1. From the Query tab of the Database window, choose New
and choose Design View.
- 2. Click the Totals button on the toolbar.
- 3. Double-click the field(s) you want to include in your query.
To find the standard deviation of one value for an entire table, create a
query with just that field. Choose StDev in the Total row.
- 4. To find the standard deviation of one value for a selected set of records,
choose criteria for which values you want to select. Include the evaluation field
in the Query Design grid. Uncheck the show box for all fields except the evaluation
field and choose StDev in the Total row.
- 5. To find the standard deviation for a group of values, insert the field(s)
you will group in a column and the evaluation field to check for standard
deviation in another column. Choose StDev in the Total row of the numeric field.
The grouping field(s) has Group By in the Total row. This query will not show the
detail for each record.
- 6. When finished, display and then save the query.
NOTE: To show the detail and the standard deviation
of these groups in a report, create a report that is grouped on a field.
In the report's group footer, add a text box and type = StDev ([Fieldname])
where Fieldname is the numeric field to check for standard deviation.
Calculations: Sum of Values
The Sum function is the most used function of all. Sum will total
all values in a field. You can create a query that will sum an entire table
or show the sum for groups of records. In a query you cannot show both
the sums and the detail of the records. For that purpose, you need to create a report.
Steps
- 1. From the Query tab of the Database window, choose New
and choose Design View.
- 2. Click the Totals button on the toolbar.
- 3. Double-click the field(s) you want to include in your query.
To find the total of one field for an entire table, create a query with just
that field. Choose Sum in the Total row.
- 4. To find the total of one field for a selected set of records, choose
criteria for which fields you want to select. Include the evaluation field
in the Query Design grid. Uncheck the show box for all fields except the evaluation
field and choose Sum in the Total row.
- 5. To find the total for a group(s) of fields, insert the field(s) you
will group in a column and the evaluation field to total in another column.
Choose Sum in the Total row of the numeric field. The grouping field(s) has Group
By in the Total row. This query will not show the detail for each record.
NOTE: To show the detail and the total of these
groups in a report, create a report that is grouped on a field. In the report's
group footer, add a text box and type =Sum([Fieldname]) where
Fieldname is the numeric field to maximize. You can also type =Sum([Fieldname])
in a text box in the report footer to find the total for all records displayed in
the report.
To show the total of values in a form (whether you can see all values or not),
add a text box to the form footer and type =Sum([Fieldname]) where
Fieldname is the numeric field to maximize. Tabular forms make the
most sense for this kind of calculation.
Conversion Functions
If you are using a form to prompt for user input, the form can have unbound
text boxes where you cannot define a data type. The same is true for
the VBA InputBox function. In these and other programming
cases, there might be instances where you have to use conversion functions to convert
the data type of your input. The conversion in some cases actually corresponds to
the data type and length property for numbers.
Steps
- 1. If the expression is in a form or report, open in Design
View. Double-click the control containing the expression to open the
Properties sheet. Click in the Control Source property (on the Data tab).
- If the expression is in a query, open the query in Design View and click
in the field with the expression.
- 2. Position the insertion point directly before a fieldname or control
name and type one of the following most common conversion functions: CCur
(currency), CDate (date), CStr (text), CDbl (Number-double),
CSng (Number-single), CInt (Number-integer).
- 3. Enclose the fieldname in parenthesis.
- 4. When finished, display and then save the form, report, or query.
Crosstab Query: Create
Crosstab queries enable you to summarize data by two or more variables. These
queries are more compact than Total queries. The first column of the result shows
one variable (called the Column Heading). The first row of the result shows
another variable (called the Row Heading). The intersection of a column and
row displays the summary of a third variable (called the Value) for only values that
match the Row and Column heading. Within reports, you can also use pivot tables
(see "Pivot Table: Create") to create the same effect.
Row and Column headings are often text or date fields, and the Value field
is often a number or currency data type.
Steps
- 1. On the Query tab of the Database window, choose New and
double-click Crosstab Query Wizard.
- 2. In the first step of the Crosstab Query Wizard, double-click the name
of the table or query.
- 3. Double-click up to three fields for the Row Headings; choose Next.
Double-click one field for the Column Headings.
- 4. Choose the value you want to summarize in the Fields list, and how
you want to summarize it in the Functions list (such as Sum, Count,
and so on). If you want a summary of each row, check Yes, Include Row Sums; choose
Next.
- 5. On the last dialog box of the wizard, type a name of the query
and choose Finish. View the results of the query and save it.
NOTE: When you use a date field for Row or Column
Headings, you probably want to group the values into time units such as month
or year. In the Query Design grid, change the Field entry from the field name
to an expression using the DatePart function. DatePart("yyyy",DateField)
will group by year. DatePart("m",DateField) will group
by month number. You can also use the Format function.
Crosstab Query: Fix Column Headings
Sometimes the order of Crosstab Headings is not appropriate. For example, if you
use the Format(DateField,"mmm") function, the
months will appear in alphabetical order (Apr, Aug, Dec, Feb, and so on) rather than
date order.
NOTE: The mmm indicates abbreviated
month names (Apr, Feb, and so on). An mmmm would indicate full spelling
for the months (January) but would still give an alphabetical order.
Steps
- 1. Open the Crosstab Query in Design View.
- 2. If the properties window is not visible, right-click the title bar
and choose Properties.
- 3. Click the Column Headings property and type the order you want
the columns to appear, separating each entry with a semicolon (;).
- 4. View, save, and close the query.
NOTE: Access will display all fields in the Column
Headings property whether there is data there or not. If you misspell an entry, that
column will appear and the correct data will not.
Expression: Operators
Most people are familiar with arithmetic operators: plus (+), minus (-), times
(*), and divide by (/). There are also other expressions you will use in criteria
and calculations. These include greater than (>), less than (<),
greater than or equal to (>=), less than or equal to (<=),
and NOT (opposite of the expression that follows). Two connectors
are OR (which indicates that any of the expressions need to be true for
the expression to be true), and AND (which indicates both expressions need
to be true for the expression to be true). Use the ampersand (&) to
connect text strings.
Arithmetic expressions are evaluated based on rules of precedence--that
is, the order in which the operations in an expression are carried out. The order
is determined by the type of operation, its location in the expression from left
to right, and whether the operation is surrounded by parentheses. Parentheses take
top priority. Then comes multiplication and division, and finally addition and subtraction.
1+2*3 would first do 2*3 (=6) and then add the 1 to equal 7. (1+2)*3 would evaluate
to 9. You can type the expressions or use the expression builder. (See "Expression:
Using the Builder.")
Steps
- 1. Open an existing or new query in Query Design View.
- 2. To create a calculation, click the Field row, type a name for
the new calculated field and a colon (:), and then type the expression.
You can also use the build icon to run the expression builder to help you build the
expression.
- 3. To select data, click any of the criteria rows (starting with
the Criteria row, then use the Or row, and then any blank rows below). Type the expression.
- 4. Finish building the query, view the results, and save it.
NOTE: To create an expression in a report
or form, open the form or report in design view. Choose the Text Box tool
on the Toolbox. Start with an equal sign (=) and then type the expression.
Expression: Refer to Controls
Referring to controls is similar to referring to field names. (See "Expression:
Refer to Field Names.") If the control is on the current form
or report, you can just type the control name in square brackets. However,
if you are referring to another open form or report, you need to include the object
type and object name.
NOTE: The form or report must be open to access
the value from a control.
Steps
- 1. In an expression, type Forms or Reports.
- 2. Type an exclamation mark (also called a bang).
- 3. Type the name of the form or report and another exclamation mark.
- 4. Type the name of the control.
NOTE: If the name of the form, report, or control
includes a space, type square brackets around the name. An example is Forms![Names
and Addresses]![Dues Amount].
Expression: Refer to Field Names
Queries, forms, reports, and even tables can refer to field names in criteria,
calculations, and properties. You might need to use one field name for another field
name's criteria. For example, to find all shipped dates that were after the customer's
required date, include >[Required Date] in the query's criteria
row of the shipped date's field.
Steps
- 1. Move to the appropriate place in an expression.
- 2. Type an open square bracket, the field name, and a close square bracket.
An example is [First Name].
Access will automatically put square brackets around field names if there is no
space in the name. However, it is a good habit to include brackets around all field
names.
Expression: Using the Builder
You can use the Expression Builder to create both simple and complex expressions
for use in tables, forms, queries, and reports. Whenever a field, value, or control
takes an expression, you will see a build button with an ellipsis (...) on it or
you can use the Build button on the toolbar.
An expression takes a set of values, symbols, operators, or identifiers and evaluates
it to produce a result. Often the result is a value, be it numeric, text, logical
Yes/No, dates and times, and so on. (See "Expression: Operators.")
Steps
- 1. In the Control Source of a form or report control, cell
on the Query Design grid, or other property, click the build
button (...) to view the Expression Builder.
- 2. To find a field control name to use in the expression, double-click
the first column on Tables, Queries, Forms, or Reports and choose the appropriate
object name. Then double-click the field or control name in the second column.
- 3. To use an operator (such as plus, greater than, and so on), click the
button in the Expression Builder window. If it is a rarely-used operator, double-click
the Operators folder in the first column of the lower portion of the Expression Builder.
Then choose a category in the second column and double-click the operator in the
third column.
- 4. To use a function, double-click Functions in the first column
and then Built-In Functions. Choose a category in the second column, and double-click
the function in the third column. The function shows <<place holders>>
where arguments should go. Click each placeholder and type your variables,
use the Expression Builder items, or delete the placeholder.
- 5. When finished with the function, choose OK.
NOTE: If you don't know the category of a function,
choose <All> in the second column of the Expression Builder, and click and
scroll in the third column. Type the first few letters of the function to scroll
down to those letters.
You can also use functions created in VBA. Double-click Functions in the first
column and choose the second folder which is the name of your database.
Access checks the syntax of your expression when you close the Expression
Builder to see if it violates any obvious rules; if it doesn't, it allows you to
enter that expression. Just because the expression's syntax checks out doesn't mean
that you've created the correct expression. But at least you have an expression template
interface to speed up your work, and to take much of the drudgery out of entering
the symbols and data correctly.
Expressions: Test Values
The syntax you use in an expression is important. In controls on reports and forms,
an expression requires the use of a preceding equal sign, but not in queries. Text
is often required to be surrounded by quotation marks. In addition to spelling function
and field names correctly, make sure you enclose field names with spaces in square
brackets. To test an expression, follow these steps.
Steps
- 1. In the Table Design property, Query Design grid
cell (Field or Criteria), or Control Source or other property on a form
or report control, type the expression.
- 2. Click outside of the box you are typing in. If you have a syntax problem,
Access will often give you a message. Common errors include not enough parentheses,
missing one or both of the brackets for field names, or forgetting an operator such
as the ampersand (&).
- 3. Display the table, query, form, or report. Access might give you an
error message in the object.
- 4. If your expression was in a field, try entering data in that field.
If the expression is calculated using other fields, try entering sample data in those
fields.
- 5. If you still have problems with an expression that includes a function,
go back to Design view, double-click the function name, and press F1 to view help
for that function.
Functions: Financial
If you need to calculate payments or interest rates, you would probably create
a calculated field on a query (Field row of Query Design grid) or a
form or report (Control Source property of a text
box for a calculated field).
Steps
- 1. In the Control Source of a form or report control, a
cell on the Query Design grid, or other property, click the
build button (...) to view the Expression Builder.
- 2. To use a financial function, double-click Functions in the first
column and then Built-In Functions. Choose Financial in the second column, and double-click
one of the following functions listed in the third column.
- 3. The function shows <<place holders>>
where arguments should go. Click each placeholder and type your variables
(which include field names in brackets). You can use the Expression Builder for field
names or other functions. You can also delete the placeholder.
- 4. When finished, display and then save the form, report, or query.
The following list includes some of the common functions you would use in the
preceding Step 2. You will replace the arguments in brackets with field names or
numeric values.
- Payment--Pmt(rate, nper, pv) The amount you need to pay on a car
or house loan or money you would get back on a loan.
- Future Value--FV(rate, nper, pmt) The value in the future when
you make a series of payments at one rate.
- Present Value--PV(rate, nper, pmt) The value in the present of
a series of payments at one rate.
- Internal Rate of Return--IRR(values) The rate your money is earning.
Unlike the other three functions, this function will probably be in a report's
group footer or report footer.
CAUTION: When you work with financial functions,
make sure the rate (interest rate) is for the same time period as nper
(number of payments). So if nper refers to monthly payments, you will have
to divide the annual interest rate by 12 to get monthly interest.
NOTE: These functions show arguments that
are required. The arguments include rate for interest rate, nper
for number of periods, pmt for payment amount, and pv for amount
(present value) of loan. For more information on each function, highlight the function
name in the expression or Expression Builder and press F1.
Functions: IIf
The IIf function enables you to test an expression and return
one result if the value is true and another if the value is false. The syntax
is IIf(expr,truepart,falsepart). You would most likely type this
expression in the Field cell of a Query Design grid. An example might be IIf([DueDate]>Date(),"Call
Now",""), which says if the value of the DueDate field is greater
than today, show the text Call Now, otherwise show nothing (a zero-length string).
- Steps
1. Click in the Control Source of a form or report control,
cell on the Query Design grid, or other property.
- 2. Type IIf, and an open parenthesis (. Then, type an expression
that will evaluate to true or false.
- 3. Type a comma and type what you want to display if the expression in
Step 2 evaluates to true. If you want text to display, include the text in quotes.
- 4. Type another comma and type what you want to display if the expression
in Step 2 evaluates to false. Again, if you want text to display, include the text
in quotes.
- 5. Finish the IIf expression with a close parenthesis ).
- 6. When finished, display and then save the form, report, or query.
Functions: User-Defined--Use
You use a user-defined function just like you use a built-in function.
You can use the function in the same places you do any other function. These include
a field property in Table Design grid, Field cell to create
calculated field in Query Design grid, criteria in Query Design grid or Advanced
Filter grid, Control Source property of a calculated control on a form
or report, and property of a query field or control on a report or form.
Steps
- 1. Click in the Control Source of a form or report control,
cell on the Query Design grid, or other property.
- 2. In a Control Source property, type an equal sign. For all expressions,
type the user-defined function, an open parenthesis, any arguments, and a
close parenthesis.
- 3. If you can't remember the function or syntax, click the Build
button. Double-click the Functions folder in the first column. Double-click the folder
with your database name.
Single-click the module name in the second column. To insert a function
with its requested prompts, double-click the function in the third column.
Functions: User-Defined--View
If Access does not have the functions you want, you can create your own in a Visual
Basic statement in a module. To see examples of user-defined functions, open
one of the example databases supplied with Access. The Solutions file provides the
richest assortment of functions (see "Database: Examples" in the Table
and Database Design part of this book).
Steps
- 1. To view a function created in a module, in the Database window,
highlight the module name and choose the Design button.
- 2. In the drop-down box on the right top of the window (the Procedure
drop-down list box), choose one of the functions. Notice that the procedure
beginning with the keyword Function has the function name and any arguments
you need to supply inside the parentheses.
- After some programming statements, you will see the function name repeated, an
equal sign, and an expression. This is the definition of the function.
- 3. Close the module window. If requested, do not save changes you made.
Grouping in Queries
You can calculate expressions based on groups of records in a query. To
do so, you must establish the grouping by entering the field and selecting Group
By in the Totals row of the Query Design grid. Then you create an expression
on a different field using an aggregate function such as SUM, AVG,
COUNT, MIN, MAX, STDEV, or VAR.
Steps
- 1. Double-click each field in the Field List or enter expressions in the
Field row of the Query Design grid.
- 2. If the Totals button has not been clicked, click it now.
- 3. In the field or field(s) you want to group, choose Group By in the
Total row.
- 4. In fields that you are going to aggregate, choose one of the aggregate
functions listed earlier--such as SUM, AVG, COUNT,
MIN, MAX, STDEV, or VAR in the Totals row.
- 5. View the query results, save, and close the query.
A calculation is not stored, but rather is evaluated and displayed each time the
query is run. You cannot update the results in Datasheet view, but you can
use the results of a group calculation to update records through an Update Query.
(See "Action Query: Update Query" in the Queries and Filters
part of this book.)
Grouping in Reports
To calculate an expression for a group of records on a report (see
also "Report: Grouping" in the Forms and Reports part of this book"),
you must add a control on that report and set the Control Source property.
To sum records, you would type =Sum([Fieldname]) in the Control Source
property. To average records, you would type =Avg([Fieldname]). (See
"Calculations: Average of Values.")
Steps
- 1. Open the report in Design View, click the Text Box tool in the
Toolbox.
- 2. To sum or average a group of records, add a text box to the
group header or footer.
- 3. To sum or average all records, add a text box to the report header
or footer.
- 4. With the text box control selected, click the Properties button on
the toolbar to open the Properties sheet. 3/21/97 V&N.
- 5. Enter the expression starting with an equal sign in the Control Source
property box.
- Or, click Build and create the expression in the Expression Builder.
Pivot Table: Create
A pivot table enables you to summarize, analyze, and manipulate data in
lists and tables. When you use the PivotTable Wizard to create a pivot table,
you tell Access which fields in the list you want to arrange in rows and columns.
Pivot tables are called such because you can quickly rearrange the position of pivot
table fields to give you a different view of the table. You start the Pivot Table
Wizard when you create a new form.
One useful application of pivot tables is creating summary tables that group
large categories of data, with totals displayed for each category. A pivot table
provides similar information to the Crosstab query. (See "Crosstab Query: Create.")
NOTE: Access "cheats" and uses the
programming in Excel to create a pivot table. To do this procedure, you need to have
Excel installed.
Steps
- 1. On the Form tab of the Database window, choose New, and
choose Pivot Table Wizard. Choose OK.
- 2. After you read the description of a Pivot Table, choose Next
and then select the Table or Query on which to base the pivot table. Double-click
any fields you want to use in your pivot table. Generally, you will click at least
one text or date field and one number or currency field.
- 3. Access launches Excel where you define the column and row layout of
the pivot table. The fields are listed as buttons on the right side of the dialog
box. Drag into the DATA area the button corresponding to the data field
you want to summarize. To arrange items in a field in columns with the labels across
the top, drag the button for that field to the COLUMN area. To arrange items
in a field in rows with labels along the side, drag the button for that field to
the ROW area.
- 4. To change the aggregate function (count, sum, average) for the data
value, double-click the field name in the DATA area, choose the desired function,
and choose OK. Then choose Next and Finish.
- 5. Access returns and shows you the results of the Pivot table in a form.
Save and close the form.
NOTE: This procedure works with small numbers
of categories for rows and columns. The view on the Access form is limited to what
you can see on the screen at one time (there is no Page Down). Another alternative
is to highlight a table or query; choose Tools, Office Links, Analyze
it with Excel; and then run the pivot table directly in Excel.
Pivot Table: Modify
Because pivot tables are devices for displaying information, you cannot
manually change information in the body of the table. You can, however, change
the names of the pivot table fields and items. Access doesn't allow you to duplicate
names. If you enter an existing field or item name inadvertently, Access will rearrange
the pivot table, moving the item with that name to the location where you typed the
name. (See "Pivot Table: Create" before you complete this task.)
Steps
- 1. Open the pivot table form in Form view and choose the
Edit Pivot Table button on the bottom of the form.
- 2. Choose Data, Refresh Data to refresh the link to Access
so that the pivot table will recalculate with the latest data and display any new
results.
- 3. To edit a pivot table field or item name, select that field or item
in the pivot table. Type the new name and press Enter.
- 4. To move a field from a column to row header (or column to row), drag
the gray box with the field name to the new location. If your pivot table has too
many columns and a few rows, for example, you may want to switch the column and row
headers.
- 5. When finished, choose File, Close & Return to Pivot
Form on Excel's menu. View, save, and close the form.
TIP: To change additional options for a specific
pivot table field, double-click the field button. Choose the options you want in
the PivotTable Field dialog box; then choose OK.
Running Sum and Count of Records
A running sum keeps adding the field instead of giving you the value of
the field. You can set the running sum to go over the whole report or start
again with each change in grouping. If you are working on a budget, for example,
you might want to see how the expenses are piling up by date.
NOTE: To count the number of records in each
group or report, create a text box control. Set the Control
Source property to equal 1. Then follow the next procedure to create a running
sum.
Steps
- 1. Open a report in Design view and, if not displayed, click the
Field List button to display the list of fields in the report.
- 2. Drag the field that you want a running sum of to the detail section.
If desired, move the field's label.
- 3. If the Property sheet is not displayed, right-click the field control
and choose Properties.
- 4. Move to the Running Sum property and double-click to choose Over Group
to reset the number when the group changes or Over All for the entire report.
- 5. View, save, and close the report.
Text: Combining
When you have more than one text field that you would like to combine on a query,
form, or report, use the ampersand (&) operator. If you
have any constant text, enclose that in quotes. A common example for what you would
type is [First Name]&" "&[Last Name].
Steps
- 1. Create a text box in Form or Report Design view, right-click
the text box, and move to the Control Source property. Or go to the
Field cell in a Query Design grid.
- 2. Type = in a form or report control, or the name of the column
header and a colon in a query.
- 3. Type the field name in square brackets.
4.Type a space and an ampersand (&).
- 5. If you have any connecting text (including spaces), type the text in
quotes. Repeat Steps 3 and 4 as often as necessary. View your object, save,
and close.
Text: Displaying Parts
Sometimes you only need to use some of the characters of a text field on labels,
reports, or queries. Several functions enable you to choose which portion of an expression
you want.
Steps
- 1. Click in the Control Source of a form or report control,
cell on the Query Design grid, or other property.
- 2. Type one of the expressions from the following section.
- 3. When finished, display and then save the form, report, or query.
Specifying Data with Functions
Access 97 functions can display specified portions of data within your expressions.
These functions include:
- Left(stringexpr,n) Takes the n number of characters from the left
side of the stringexpr.
- Right(stringexpr,n) Takes the n number of characters from the right
side of the stringexpr.
- Mid(stringexpr,start,length) Takes out the middle of the stringexpr
beginning at start for length characters.
- InStr(stringexpr1,stringexpr2) The simplest form of this
function returns the starting position of where a stringexpr2 begins in stringexpr1.
- Trim(stringexpr) Removes any spaces at the beginning and end of
the string expression. To remove leading spaces, use LTrim; to remove
trailing spaces, use RTrim.
|
