📄 ch05.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Symantec Visual Page 1.0">
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
<TITLE>Ch 5 -- Calculations</TITLE>
</HEAD>
<BODY BACKGROUND="../buttonart/bg1.gif" BGCOLOR="#FFFFFF">
<P>
<TABLE BORDER="0" WIDTH="600">
<TR>
<TD WIDTH="80" VALIGN="TOP"><IMG SRC="../buttonart/que.gif" WIDTH="75" HEIGHT="24" ALIGN="BOTTOM" BORDER="0"><BR>
<BR>
<A HREF="index.htm"><IMG
SRC="../buttonart/toc.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="TOC" BORDER="0"
NAME="toc4"></A><BR>
<A HREF="ch04.htm"><IMG
SRC="../buttonart/back1.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="BACK" BORDER="0"
NAME="toc1"></A><BR>
<A HREF="ch06.htm"><IMG
SRC="../buttonart/forward.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="FORWARD"
BORDER="0" NAME="toc2"></A><BR>
<A HREF="index.htm"><IMG
SRC="../buttonart/home.gif" WIDTH="40" HEIGHT="40" ALIGN="BOTTOM" ALT="HOME" BORDER="0"
NAME="toc3"></A></TD>
<TD WIDTH="520" VALIGN="TOP">
<H2><FONT COLOR="#000077">Microsoft® Access 97 Quick Reference</FONT></H2>
<P>
<H1 ALIGN="CENTER"><FONT COLOR="#000077">- 5 -</FONT><BR>
<FONT COLOR="#000077">Calculations</FONT></H1>
<P>You can use calculations and <I>expressions</I> throughout Access in all <I>objects</I>--<I>tables</I>,
<I>queries</I>, <I>forms</I>, <I>reports</I>, and even <I>macros</I> and <I>modules</I>.
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 <I>field</I>. You can type calculations directly or use
the Expression Builder to help you.</P>
<P>In a query, calculations can be done by creating a new field based on the values
of other fields in the query <I>grid</I>. You can also create grouping types of queries
with crosstab and total queries.</P>
<P>In forms and reports, you generally add <I>text boxes </I>for calculations. These
text boxes, as well as everything else on the form or report, are called <I>controls</I>,
You learn more about forms and reports in the next part of this book, "Forms
and Reports."
<H2><FONT COLOR="#000077"><B>Blanks and Nulls in Calculations</B></FONT></H2>
<P>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 <I>null</I> 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.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1.</B> If the expression is in a form or report, open in <I>Design</I> <I>View</I>.
Double-click the control containing the expression to open the Properties sheet.
Click in the Control Source <I>property</I> (on the Data tab).
<P>
<DD>If the expression is in a query, open the query in Design View and click in the
field with the expression.
<P>
<DD><B>2.</B> If necessary, press Shift+F2 to zoom and see the whole expression.
<P>
<DD><B>3.</B> If your <I>expression</I> evaluates to a numeric value and one of your
<I>fields</I> is <I>null</I>, the whole expression evaluates to null. You can use
the <TT>NZ</TT> <I>function</I> to convert any field to another number. Click before
the field name and type <B>NZ</B>. Enclose the field name in parentheses. For addition
and subtraction, use <TT>NZ(</TT>Fieldname<TT>)</TT> to convert null values to zero.
For multiplication and division, use <TT>NZ(</TT>Fieldname<TT>,1)</TT> to convert
null values to 1.
<P>
<DD><B>4. </B>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 <B>[Field1]&"</B> <B>"&[Field2]</B>.
<P>
<DD><B>5. </B>If you don't want extra spaces in text strings, you might need to use
Is Null. The <TT>IsNull</TT> 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:
<TT>Name: [Firstname] & " " & IIf(IsNull([Middlename]),"",[Middlename]
& " ") & [Lastname]</TT>
<P>
<DD><B>6. </B>When finished, display and then save the form, report, or query.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> In a Totals <I>Query</I>, Access does not
include null values for <I>aggregate functions</I> such as Sum, Count, and Average.
If you want to count all records including nulls, use <TT>Count(*)</TT>.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Calculated Fields: Forms and Reports--Create by Typing</B></FONT></H2>
<P>You can enter a calculation in a <I>form</I> by typing it or using the Expression
Builder. When you enter a <I>field</I> 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.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In <I>Design View</I> of a form or report, click the <I>Text Box</I>
tool and then click the form or report and type an equal sign (<B>=</B>).
<P>
<DD><B>2.</B> If you are including a <I>function</I>, include the function name with
parentheses (<I>arguments</I> might or might not go inside parentheses).
<P>
<DD><B>3. </B>If the next part of the expression is a field, include field in square
brackets, as in <TT>[</TT>Fieldname<TT>]</TT>.
<P>
<DD><B>4.</B> If you are doing arithmetic operations, include + - * /, or if you
are combining text, include the ampersand (&).
<P>
<DD><B>5. </B>When finished, click outside the text box.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> Only <I>controls</I> that have Control
Source properties can take an expression with a calculated result. Expressions can
be any valid Access function or <I>operator</I>, values, fields, or identifiers,
and must start with an equal sign.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Calculated Fields: Forms and Reports--Edit Expression</B></FONT></H2>
<P>After you type an <I>expression</I> in a <I>text box</I>, 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.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1.</B> Open the <I>form</I> or <I>report</I> in Design<I> </I>View.
<P>
<DD><B>2.</B> If the <I>Property</I> sheet is not displayed, right-click the text
box, choose <U>P</U>roperties, and click the Data tab.
<P>
<DD><B>3.</B> Click the Control Source property and edit the expression or press
Shift+F2 to zoom. When done with the zoom window, choose OK.
<P>
<DD><B>4.</B> Save and close the form or report.
</DL>
<H2><FONT COLOR="#000077"><B>Calculated Fields: Queries--Create by Typing</B></FONT></H2>
<P>Because you do not want to store unnecessary data in a <I>table</I>, you often
need to do calculations in a <I>query</I>. To create a calculated <I>field</I> in
a query, you use a Field cell in the Query Design <I>grid</I>. Calculated fields
in a query are not stored to disk, but are recalculated at the query's runtime.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>Open the query in <I>Design view</I> and click an unused Field cell.
<P>
<DD><B>2. </B>Type the name for the field followed by a colon (<B>:</B>).
<P>
<DD><B>3. </B>If the next part of the <I>expression</I> is a field, include field
in square brackets, as in <TT>[</TT>Fieldname<TT>]</TT>.
<P>
<DD><B>4.</B> If you are doing arithmetic operations, include + - * /, or if you
are combining text, include &.
<P>
<DD><B>5. </B>Repeat field names and operators as much as necessary, and then click
outside of the field box. If there is a <I>syntax</I> error, Access will give you
a warning message.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To edit the expression, click the Field
cell. If necessary, press Shift+F2 to zoom to a larger window.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Calculations: Average of Values</B></FONT></H2>
<P>The <TT>AVG</TT> <I>function</I> 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.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>In the <I>Database window</I>, click the table or query on which you
want to base the new query and choose <U>Q</U>uery from the New Object button on
the toolbar. Double click any fields in the Field List you will use for the query.
<P>
<DD><B>2. </B>To find an average of one value for an entire <I>table</I>, create
a <I>query</I> with just that field. Click the Totals button on the toolbar and choose
Avg in the Total row.
<P>
<DD><B>3.</B> 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 <I>grid</I>. 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.
<P>
<DD><B>4. </B>To find the average for a group of values, create a query and put the
field(s) you will <I>group</I> 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 <I>record</I>, just the averages for the groups.
<P>
<DD><B>5. </B>Save the query and click the Run button on the toolbar.
</DL>
<BLOCKQUOTE>
<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To show the detail and the average of these
groups in a <I>report</I>, create a report that is grouped on a field. In the report's
group footer, add a <I>text box</I> and type <B>=Avg([<I>Fieldname</I>])</B> where
<B><I>Fieldname</I></B> is the numeric field to average.
<HR>
</BLOCKQUOTE>
<H2><FONT COLOR="#000077"><B>Calculations: Count of Items in List</B></FONT></H2>
<P>The easiest way to see a count of all records (or selected records) in a <I>table</I>
is to look at the navigation buttons at the bottom of a table or <I>Query</I> window
after the word <TT>of</TT>. 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 <I>group</I>.
<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
<DL>
<DD><B>1. </B>From the Query tab of the <I>Database window</I>, choose <U>N</U>ew
and choose <I>Design View</I>.
<P>
<DD><B>2. </B>Click the Totals button on the toolbar.
<P>
<DD><B>3.</B> Double-click the <I>field</I> you want to group by (the Total row defaults
to Group By).
<P>
<DD><B>4. </B>Double-click a field where every <I>record</I> has a value (the <I>primary
key</I> is a good choice). Choose Count in the Total row.
<P>
<DD><B>5. </B>Run the query, save, and close.
</DL>
<H2><FONT COLOR="#000077"><B>Calculations: Date</B></FONT></H2>
<P>Date<I> </I>fields are common in <I>databases</I>. 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.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -