ch05.htm

Microsoft_Access_97_Quick_Reference.zip

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>If the <I>expression</I> is in a <I>form</I> or <I>report</I>, open
				in <I>Design</I> <I>View</I>. Double-click the <I>control</I> containing the expression
				to open the Properties sheet. Click in the Control Source <I>property</I> (on the
				Data tab).
				<P>
				<DD><B>2. </B>If the expression is in a <I>query</I>, open the query in Design View
				and click in the field with the expression.
				<P>
				<DD><B>3.</B> If the expression is for a <I>field</I> in a <I>table</I>, 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 <I>Validation </I>Rule property.
				<P>
				<DD><B>4. </B>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 <U>P</U>roperties.
				Click in the Validation Rule box.
				<P>
				<DD><B>5.</B> Type one of the expressions listed in the following section. When finished,
				save and display the table, query, form, or report.
			</DL>
			<H3><FONT COLOR="#000077"><B>Using Date and Time Expressions</B></FONT></H3>
			<P>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.
			<UL>
				<LI><B>Date() </B>You enter today's date as <B>Date()</B>. This is often the Default
				<I>property</I> in the <I>Table</I> Design <I>grid</I> or <I>Form</I> Design property
				of a <I>control</I>. This also is combined with an <I>operator</I> such as <TT>&lt;Date()</TT>
				as the <I>Validation</I> Rule property <I>expression</I> or in the criteria cell
				of the <I>Query</I> Design grid. In the <I>Field</I> cell of Query Design grid, <TT>Date()</TT>
				is often combined with other date fields as in <TT>DaysLate: Date()-[DueDate]</TT><B>.</B>
				Use <TT>Now()</TT> if you need to include the current time.
				<P>
				<LI><B>DateDiff(</B>interval, date1, date2<B>) </B>If you want to find the difference
				between two dates in weeks or months, use <TT>DateDiff(</TT>interval, date1, date2<TT>)</TT>.
				Enclose the interval argument in quotes such as <TT>&quot;ww&quot;</TT> or <TT>&quot;m&quot;</TT>.
				See the upcoming note for options on the interval. You would generally use this <I>function</I>
				in a Field cell of the Query Design grid or in the Control Source property of a calculated
				control on a form or <I>report</I>.
				<P>
				<LI><B>DateAdd(</B>interval, number, date<B>) </B>If you want to find a date in the
				future, use <TT>DateAdd(</TT>interval, number, date<TT>)</TT> in the same places
				and with the same values for interval as <TT>DateDiff</TT>. Number is the number
				of intervals.
				<P>
				<LI><B>DatePart(</B>interval,date<B>) </B>To convert a date field to the month or
				year portion of the date (or any other interval below), use <TT>DatePart(</TT>interval,date<TT>)</TT>.
				When you use this function, it is often in the Field cell of Query Design grid of
				Total and Crosstab queries.
				<P>
				<LI><B>Format(</B>expression,fmt<B>) </B>If <TT>DatePart</TT> doesn't give you enough
				options, you can also use <TT>Format(</TT>expression, fmt<TT>)</TT> where expression
				can be a date field and fmt can be the same as interval below with additional choices
				(<TT>y</TT> = year; <TT>yy</TT>=year with 2 digits--97 or 01; <TT>yyyy</TT> = four-digit
				year; <TT>m</TT> = month number; <TT>mm</TT> = month number with leading 0; <TT>mmm</TT>
				= month abbreviation; <TT>mmmm</TT> = full month name). You enclose the <TT>fmt</TT>
				value in quotes also. Format is often used in Total and Crosstab queries.
			</UL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> The interval options for the <TT>DateDiff</TT>,
			<TT>DateAdd</TT>, and <TT>DatePart</TT> functions include <TT>yyyy</TT> (Year), <TT>q
			</TT>(Quarter), <TT>m</TT> (Month), <TT>y</TT> (Day of year), <TT>d</TT> (Day), <TT>w
			</TT>(Weekday), <TT>ww</TT> (Week), <TT>h</TT> (Hour), <TT>n </TT>(Minute), and <TT>s</TT>
			(Second). For formatting numbers, see also &quot;Query: Format Field&quot; in the
			Queries and Filters part of this book, and &quot;Format: Numbers and Dates&quot;
			in the Forms and Reports part of the book. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Calculations: First and Last Values</B></FONT></H2>
			<P>When you want to see the first or last value in a list, you can use the <TT>First</TT>
			and <TT>Last</TT> functions. You need to sort the list or the results of these functions
			will be arbitrary.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>On the <I>Query</I> tab of the <I>Database window</I>, choose the <U>N</U>ew
				button and double-click <I>Design View</I>.
				<P>
				<DD><B>2.</B> In Query Design view, click the Totals button.
				<P>
				<DD><B>3. </B>Double-click the <I>field</I> to sort and choose <I>Group</I> By in
				the Total row.
				<P>
				<DD><B>4. </B>Double-click the field to find the order, and choose First or Last
				in the Total row.
				<P>
				<DD><B>5. </B>View, save, and close the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> You can also see <TT>First</TT> and <TT>Last</TT>
			values in a <I>report</I>. Create a report that is grouped by one field. In the Group
			Footer section, create a calculated <I>control</I> and use the <TT>First</TT> or
			<TT>Last</TT> functions. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Calculations: Maximum and Minimum</B></FONT></H2>
			<P>The Maximum and Minimum functions help you find the highest and lowest values
			of a <I>field</I>. You can also find these values by sorting a <I>table</I> on the
			field of interest Descending or Ascending and looking at the first <I>record</I>.
			Unlike the <TT>Avg</TT> or <TT>Sum</TT> functions, the field you are finding the
			maximum or minimum value of can be a text or date field in addition to a number <I>data
			type</I>. The following items show just the maximum (<TT>MAX</TT>) <I>function</I>.
			You can use these identically with the minimum (<TT>MIN</TT>) function.
			<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(s) </I>you want to include in your query.
				To find the maximum of one value for an entire table, create a <I>query</I> with
				just that field. Choose Max in the Total row.
				<P>
				<DD><B>4. </B>To find the maximum of one value for a selected set of records, choose
				<I>criteria</I> for the values that you want to select. Include the evaluation field
				in the Query Design <I>grid</I>. Uncheck the show box for all fields except the evaluation
				field. In the evaluation field, choose Max in the Total row.
				<P>
				<DD><B>5. </B>To find the maximum for a group of values, insert the field(s) you
				will <I>group</I> 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.
				<P>
				<DD><B>6. </B>When finished, display and then save the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To show the detail and the maximum 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>=Max([<I>Fieldname</I>])</B> where
			<B><I>Fieldname</I></B> is the numeric field to maximize. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Calculations: Percent</B></FONT></H2>
			<P><I>Percent</I> means per 100. When you look for percentages, you are often dividing
			a <I>field</I>'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 <I>report</I> or <I>form</I>.</P>
			<P>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 <I>query</I>, form, or report to multiply a percent. (See &quot;Calculated
			Fields: Queries--Create by Typing.&quot;)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To increase an amount by a percentage,
			don't forget to include the amount itself by including a &quot;1&quot; in the multiplier.
			For example, to increase prices by 5 percent, you would type <B>[Price]*1.05</B>.
			Unlike Excel, you cannot type <B>105%</B> in the formula, and you must use the decimal
			equivalent of the number. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Create a <I>text box</I> in <I>Form</I> or <I>Report view</I>, right-click
				the text box, and move to the <I>Control</I> Source <I>property</I>.
				<P>
				<DD><B>2. </B>Type <B>=[</B>Fieldname<B>]/Sum([</B>Fieldname<B>])</B> where Fieldname
				is the value for which you want to find the percent.
				<P>
				<DD><B>3.</B> Move to the Format <I>property</I>. Choose Percent from the drop-down
				list of choices. Type the number of decimal places you want in the Decimal Places
				property.
				<P>
				<DD><B>4. </B>View, save, and close the form or report.
			</DL>
			<H2><FONT COLOR="#000077"><B>Calculations: Standard Deviation and Variance</B></FONT></H2>
			<P>Standard deviation (<TT>STDEV</TT>) and variance (<TT>VAR</TT>) give you an idea
			how much your data varies throughout the whole <I>table</I>. 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 <I>field</I> is spread across many numbers. Both
			functions work the same way ; <TT>STDEV</TT> is used in the steps of the following
			task. For variance, replace <TT>STDEV</TT> with <TT>VAR</TT>. Both of these functions
			must be used with number (or currency) fields.
			<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(s) </I>you want to include in your query.
				To find the standard deviation of one value for an entire <I>table</I>, create a
				<I>query</I> with just that field. Choose StDev in the Total row.
				<P>
				<DD><B>4.</B> 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 <I>grid</I>. Uncheck the show box for all fields except the evaluation
				field and choose StDev in the Total row.
				<P>
				<DD><B>5.</B> To find the standard deviation for a group of values, insert the field(s)
				you will <I>group</I> 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 <I>record</I>.
				<P>
				<DD><B>6. </B>When finished, display and then save the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To show the detail and the standard deviation
			of these <I>groups</I> 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>= StDev ([<I>Fieldname</I>])</B>
			where <B><I>Fieldname</I></B> is the numeric field to check for standard deviation.
			
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Calculations: Sum of Values</B></FONT></H2>
			<P>The <TT>Sum</TT> <I>function</I> is the most used function of all. Sum will total
			all values in a <I>field</I>. You can create a query that will sum an entire <I>table</I>
			or show the sum for groups of <I>records</I>. In a <I>query</I> you cannot show both
			the sums and the detail of the records. For that purpose, you need to create a report.