ch05.htm

Microsoft_Access_97_Quick_Reference.zip

			<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 total of one field for an entire table, create a <I>query</I> with just
				that field. Choose Sum in the Total row.
				<P>
				<DD><B>4.</B> To find the total of one field for a selected set of records, choose
				<I>criteria</I> for which fields 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 Sum in the Total row.
				<P>
				<DD><B>5. </B>To find the total for a group(s) of fields, insert the field(s) you
				will <I>group</I> 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 <I>record</I>.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To show the detail and the total 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>=Sum([<I>Fieldname</I>])</B> where
			<B><I>Fieldname</I></B> is the numeric field to maximize. You can also type <B>=Sum([<I>Fieldname</I>])</B>
			in a text box in the report footer to find the total for all records displayed in
			the report.<BR>
			To show the total of values in a <I>form</I> (whether you can see all values or not),
			add a text box to the form footer and type <B>=Sum([<I>Fieldname</I>])</B> where
			<B><I>Fieldname</I></B> is the numeric field to maximize. Tabular forms make the
			most sense for this kind of calculation. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Conversion Functions</B></FONT></H2>
			<P>If you are using a <I>form</I> to prompt for user input, the form can have <I>unbound</I>
			<I>text boxes</I> where you cannot define a <I>data type</I>. The same is true for
			the <I>VBA</I> <TT>InputBox</TT> <I>function</I>. 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 <I>property</I> for numbers.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> If the <I>expression</I> is in a form 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 property (on the Data tab).
				<P>
				<DD>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>2. </B>Position the insertion point directly before a fieldname or control
				name and type one of the following most common conversion functions: <TT>CCur</TT>
				(currency), <TT>CDate</TT> (date), <TT>CStr</TT> (text), <TT>CDbl</TT> (Number-double),
				<TT>CSng</TT> (Number-single), <TT>CInt</TT> (Number-<I>integer</I>).
				<P>
				<DD><B>3. </B>Enclose the fieldname in parenthesis.
				<P>
				<DD><B>4. </B>When finished, display and then save the form, report, or query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Crosstab Query: Create</B></FONT></H2>
			<P>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 <I>variable</I> (called the Column Heading). The first row of the result shows
			another variable (called the Row<I> </I>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 <I>pivot tables
			</I>(see &quot;Pivot Table: Create&quot;) to create the same effect.</P>
			<P>Row and Column headings are often text or date fields, and the Value <I>field</I>
			is often a number or currency <I>data type</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>On the Query tab of the <I>Database window</I>, choose <U>N</U>ew and
				double-click Crosstab Query Wizard.
				<P>
				<DD><B>2. </B>In the first step of the Crosstab Query Wizard, double-click the name
				of the <I>table</I> or <I>query</I>.
				<P>
				<DD><B>3.</B> Double-click up to three fields for the Row Headings; choose <U>N</U>ext.
				Double-click one field for the Column Headings.
				<P>
				<DD><B>4. </B>Choose the value you want to summarize in the Fields list, and how
				you want to summarize it in the <I>Functions</I> list (such as <TT>Sum</TT>, <TT>Count</TT>,
				and so on). If you want a summary of each row, check Yes, Include Row Sums; choose
				<U>N</U>ext.
				<P>
				<DD><B>5. </B>On the last <I>dialog box</I> of the wizard, type a name of the query
				and choose <U>F</U>inish. View the results of the query and save it.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> When you use a date field for Row or Column
			Headings, you probably want to <I>group</I> the values into time units such as month
			or year. In the Query Design <I>grid</I>, change the Field entry from the field name
			to an <I>expression</I> using the <TT>DatePart</TT> <I>function</I>. <TT>DatePart(&quot;yyyy&quot;,</TT>DateField<TT>)</TT>
			will group by year. <TT>DatePart(&quot;m&quot;,</TT>DateField<TT>)</TT> will group
			by month number. You can also use the <TT>Format</TT> function. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Crosstab Query: Fix Column Headings</B></FONT></H2>
			<P>Sometimes the order of Crosstab Headings is not appropriate. For example, if you
			use the <TT>Format(</TT>DateField<TT>,&quot;mmm&quot;)</TT> <I>function</I>, the
			months will appear in alphabetical order (Apr, Aug, Dec, Feb, and so on) rather than
			date order.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> The <TT>mmm</TT> indicates abbreviated
			month names (Apr, Feb, and so on). An <TT>mmmm</TT> would indicate full spelling
			for the months (January) but would still give an alphabetical order. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> Open the Crosstab <I>Query</I> in <I>Design View</I>.
				<P>
				<DD><B>2. </B>If the properties window is not visible, right-click the title bar
				and choose <U>P</U>roperties.
				<P>
				<DD><B>3. </B>Click the Column Headings <I>property</I> and type the order you want
				the columns to appear, separating each entry with a semicolon (;).
				<P>
				<DD><B>4. </B>View, save, and close the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> 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. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Expression: Operators</B></FONT></H2>
			<P>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 (<TT>&gt;</TT>), less than (<TT>&lt;</TT>),
			greater than or equal to (<TT>&gt;=</TT>), less than or equal to (<TT>&lt;=</TT>),
			and <TT>NOT</TT> (opposite of the <I>expression</I> that follows). Two connectors
			are <TT>OR</TT> (which indicates that any of the expressions need to be true for
			the expression to be true), and <TT>AND</TT> (which indicates both expressions need
			to be true for the expression to be true). Use the ampersand (<TT>&amp;</TT>) to
			connect text strings.</P>
			<P>Arithmetic expressions are evaluated based on <I>rules of precedence</I>--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 &quot;Expression:
			Using the Builder.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open an existing or new <I>query</I> in Query Design View.
				<P>
				<DD><B>2. </B>To create a calculation, click the <I>Field</I> row, type a name for
				the new calculated field and a colon (<B>:</B>), and then type the <I>expression</I>.
				You can also use the build icon to run the expression builder to help you build the
				expression.
				<P>
				<DD><B>3. </B>To select data, click any of the <I>criteria</I> rows (starting with
				the Criteria row, then use the Or row, and then any blank rows below). Type the expression.
				<P>
				<DD><B>4. </B>Finish building the query, view the results, and save it.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To create an expression in a <I>report</I>
			or <I>form</I>, 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. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Expression: Refer to Controls</B></FONT></H2>
			<P>Referring to controls is similar to referring to <I>field</I> names. (See &quot;Expression:
			Refer to Field Names.&quot;) If the <I>control</I> is on the current <I>form</I>
			or <I>report</I>, 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 <I>object</I>
			type and object name.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> The form or report must be open to access
			the value from a control. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In an expression, type <B>Forms</B> or <B>Reports</B>.
				<P>
				<DD><B>2. </B>Type an exclamation mark (also called a <I>bang</I>).
				<P>
				<DD><B>3.</B> Type the name of the form or report and another exclamation mark.
				<P>
				<DD><B>4.</B> Type the name of the control.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> If the name of the form, report, or control
			includes a space, type square brackets around the name. An example is <B>Forms![<I>Names
			and Addresses</I>]![<I>Dues Amount</I>]</B>. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Expression: Refer to Field Names</B></FONT></H2>
			<P>Queries, forms, reports, and even tables can refer to <I>field</I> 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 <TT>&gt;[</TT>Required Date<TT>]</TT> in the query's criteria
			row of the shipped date's field.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Move to the appropriate place in an <I>expression.</I>