ch05.htm

Microsoft_Access_97_Quick_Reference.zip

			also "Report: Grouping" in the Forms and Reports part of this book"),
			you must add a <I>control</I> on that report and set the Control Source <I>property</I>.
			To sum records, you would type <B>=Sum([</B>Fieldname<B>])</B> in the Control Source
			property. To average records, you would type <B>=Avg([</B>Fieldname<B>])</B>. (See
			&quot;Calculations: Average of Values.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open the report in <I>Design View</I>, click the Text Box tool in the
				<I>Toolbox</I>.
				<P>
				<DD><B>2. </B>To sum or average a group of records, add a <I>text box</I> to the
				group header or footer.
				<P>
				<DD><B>3. </B>To sum or average all records, add a text box to the report header
				or footer.
				<P>
				<DD><B>4. </B>With the text box control selected, click the Properties button on
				the toolbar to open the Properties sheet. 3/21/97 V&amp;N.
				<P>
				<DD><B>5. </B>Enter the expression starting with an equal sign in the Control Source
				property box.
				<P>
				<DD>Or, click Build and create the expression in the Expression <I>Builder</I>.
			</DL>
			<H2><FONT COLOR="#000077"><B>Pivot Table: Create</B></FONT></H2>
			<P>A <I>pivot table</I> enables you to summarize, analyze, and manipulate data in
			lists and tables. When you use the <I>PivotTable Wizard</I> 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 <I>form</I>.</P>
			<P>One useful application of pivot tables is creating summary tables that <I>group</I>
			large categories of data, with totals displayed for each category. A pivot table
			provides similar information to the Crosstab query. (See &quot;Crosstab Query: Create.&quot;)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> Access &quot;cheats&quot; and uses the
			programming in Excel to create a pivot table. To do this procedure, you need to have
			Excel installed. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>On the Form tab of the <I>Database window</I>, choose <U>N</U>ew, and
				choose Pivot Table Wizard. Choose OK.
				<P>
				<DD><B>2. </B>After you read the description of a Pivot Table, choose <U>N</U>ext
				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.
				<P>
				<DD><B>3. </B>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 <I>dialog
				box</I>. Drag into the <U>D</U>ATA 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 <U>C</U>OLUMN area. To arrange items
				in a field in rows with labels along the side, drag the button for that field to
				the <U>R</U>OW area.
				<P>
				<DD><B>4. </B>To change the aggregate function (count, sum, average) for the data
				value, double-click the field name in the <U>D</U>ATA area, choose the desired function,
				and choose OK. Then choose <U>N</U>ext and <U>F</U>inish.
				<P>
				<DD><B>5. </B>Access returns and shows you the results of the Pivot table in a form.
				Save and close the form.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> 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 <U>T</U>ools, Office <U>L</U>inks, <U>A</U>nalyze
			it with Excel; and then run the pivot table directly in Excel. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Pivot Table: Modify</B></FONT></H2>
			<P>Because <I>pivot tables</I> are devices for displaying information, you cannot
			manually change information in the body of the <I>table</I>. 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 &quot;Pivot Table: Create&quot; before you complete this task.)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> Open the pivot table <I>form </I>in<I> Form view</I> and choose the
				Edit <U>P</U>ivot Table button on the bottom of the form.
				<P>
				<DD><B>2.</B> Choose <U>D</U>ata, <U>R</U>efresh Data to refresh the link to Access
				so that the pivot table will recalculate with the latest data and display any new
				results.
				<P>
				<DD><B>3. </B>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.
				<P>
				<DD><B>4. </B>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.
				<P>
				<DD><B>5. </B>When finished, choose <U>F</U>ile, <U>C</U>lose &amp; Return to Pivot
				Form on Excel's menu. View, save, and close the form.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT> To change additional options for a specific
			pivot table field, double-click the field button. Choose the options you want in
			the PivotTable Field <I>dialog box</I>; then choose OK. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Running Sum and Count of Records</B></FONT></H2>
			<P>A <I>running sum</I> keeps adding the field instead of giving you the value of
			the field. You can set the running sum to go over the whole <I>report</I> 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.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To count the number of records in each
			<I>group</I> or report, create a <I>text box</I> <I>control</I>. Set the Control
			Source <I>property</I> to equal 1. Then follow the next procedure to create a running
			sum. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a report in <I>Design view</I> and, if not displayed, click the
				Field List button to display the list of fields in the report.
				<P>
				<DD><B>2. </B>Drag the field that you want a running sum of to the detail section.
				If desired, move the field's <I>label</I>.
				<P>
				<DD><B>3. </B>If the Property sheet is not displayed, right-click the field control
				and choose <U>P</U>roperties.
				<P>
				<DD><B>4. </B>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.
				<P>
				<DD><B>5. </B>View, save, and close the report.
			</DL>
			<H2><FONT COLOR="#000077"><B>Text: Combining</B></FONT></H2>
			<P>When you have more than one text field that you would like to combine on a query,
			<I>form</I>, or <I>report</I>, use the ampersand (&amp;) <I>operator</I>. If you
			have any constant text, enclose that in quotes. A common example for what you would
			type is <B>[First Name]&amp;&quot; &quot;&amp;[Last Name]</B>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Create a <I>text box</I> in Form or Report <I>Design view</I>, right-click
				the text box, and move to the <I>Control</I> Source <I>property</I>. Or go to the
				Field cell in a Query Design <I>grid</I>.
				<P>
				<DD><B>2. </B>Type <B>=</B> in a form or report control, or the name of the column
				header and a colon in a query.
				<P>
				<DD><B>3. </B>Type the field name in square brackets.
				<P>
				<DD><B><BR>
				4.</B>Type a space and an ampersand (<B>&amp;</B>).
				<P>
				<DD><B>5. </B>If you have any connecting text (including spaces), type the text in
				quotes. Repeat Steps 3 and 4 as often as necessary. View your <I>object</I>, save,
				and close.
			</DL>
			<H2><FONT COLOR="#000077"><B>Text: Displaying Parts</B></FONT></H2>
			<P>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.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click in the Control Source of a <I>form</I> or <I>report</I> control,
				cell on the <I>Query</I> Design <I>grid</I>, or other <I>property</I>.
				<P>
				<DD><B>2.</B> Type one of the expressions from the following section.
				<P>
				<DD><B>3.</B> When finished, display and then save the form, report, or query.
			</DL>
			<H3><FONT COLOR="#000077"><B>Specifying Data with Functions</B></FONT></H3>
			<P>Access 97 functions can display specified portions of data within your expressions.
			These functions include:
			<UL>
				<LI><B>Left(</B>stringexpr,n<B>)</B> Takes the n number of characters from the left
				side of the stringexpr.
				<P>
				<LI><B>Right(</B>stringexpr,n<B>)</B> Takes the n number of characters from the right
				side of the stringexpr.
				<P>
				<LI><B>Mid(</B>stringexpr,start,length<B>)</B> Takes out the middle of the stringexpr
				beginning at start for length characters.
				<P>
				<LI><B>InStr(</B>stringexpr1,stringexpr2<B>)</B> The simplest <I>form</I> of this
				function returns the starting position of where a stringexpr2 begins in stringexpr1.
				<P>
				<LI><B>Trim(</B>stringexpr<B>)</B> Removes any spaces at the beginning and end of
				the <I>string</I> expression. To remove leading spaces, use <TT>LTrim</TT>; to remove
				trailing spaces, use <TT>RTrim</TT>.
			</UL>
		</TD>
	</TR>
	<TR>
		<TD WIDTH="80"></TD>
		<TD WIDTH="520">
			<P ALIGN="CENTER"><A HREF="index.htm"><IMG
			SRC="../buttonart/btoc.gif" WIDTH="41" HEIGHT="41" ALIGN="BOTTOM" ALT="TOC" BORDER="0"
			NAME="toc8"></A><A HREF="ch04.htm"><IMG
			SRC="../buttonart/bback.gif" WIDTH="41" HEIGHT="41" ALIGN="BOTTOM" ALT="BACK" BORDER="0"
			NAME="toc6"></A><A HREF="ch06.htm"><IMG
			SRC="../buttonart/bforward.gif" WIDTH="41" HEIGHT="41" ALIGN="BOTTOM" ALT="FORWARD"
			BORDER="0" NAME="toc7"></A><A HREF="index.htm"><IMG
			SRC="../buttonart/bhome.gif" WIDTH="41" HEIGHT="41" ALIGN="BOTTOM" ALT="HOME" BORDER="0"
			NAME="toc5"></A><BR>
			<BR>
			<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" BORDER="0"></P>
			<P><BR>
			<FONT COLOR="#000000">&#169;</FONT><A HREF="copy.htm"><FONT COLOR="#000000">Copyright</FONT></A><FONT
			COLOR="#000000">, Macmillan Computer Publishing. All rights reserved.</FONT>
		</TD>
	</TR>
</TABLE>



</BODY>

</HTML>