ch04.htm

Microsoft_Access_97_Quick_Reference.zip

			</DL>
			<P>For more details on the joins, see &quot;Tables: Combine with Join&quot; and &quot;Tables:
			Create a Self Join.&quot;


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you accidentally add a table
			twice to the design grid, click in the table and press the Delete <I>key</I> to remove
			it. Don't forget that you may not see all the tables. Use the scroll bars in the
			upper part of the grid to see if you have any more tables hiding in the grid. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Query: Change Column Heading Names</B></FONT></H2>
			<P>When you make calculated columns in a query, you will probably want to add a column
			name instead of the Expr headings that Access defaults to.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In Query <I>Design View</I>, click before text in any <I>Field</I>
				cell.
				<P>
				<DD><B>2. </B>Type the new column name and a colon (:).
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Create with Design View</B></FONT></H2>
			<P>Queries are recipes for finding, selecting, and sorting data in tables. Access
			stores the definition of a <I>query</I>, runs the query, and returns a result set
			when the query is requested. Queries can be used as the underlying <I>data</I> set
			for <I>forms</I> and <I>reports</I>. Queries can also be used to select, append,
			delete, and update <I>records</I> in the <I>database</I> (see the appropriate &quot;<I>Action
			Query</I>&quot; sections).</P>
			<P>You create a new query using either the Query Wizard or the Query Design window.
			Both are relatively easy to use, though the Query Wizard will walk you through the
			process step-by-step. The Query Design Wizard provides you with additional options.
			(See &quot;Query: Create with Wizard.&quot;)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You can select records by applying
			a <I>filter</I> and sort those records. Access 97 remembers the last filter you applied
			without you having to re-enter it (see the Filter tasks earlier in this part). This
			is an alternative to running some queries. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the Query tab in the <I>Database window</I>, then choose the
				<U>N</U>ew button. Select Design View in the New Query <I>dialog box</I>; then double-click
				the table(s) you want from the Show Table dialog box, choose <U>C</U>lose.
				<P>
				<DD>If you add related tables, Access will automatically create <I>join</I> lines
				between the tables.<BR>
				To create a <I>relationship</I> between two tables, drag the <I>key field</I> of
				the parent tables to the related <I>field</I> of the <I>child</I> table.
				<P>
				<DD><B>2.</B> Double-click any fields from the Field Lists for each table you want
				as part of the query.
				<P>
				<DD><B>3. </B>Click in the Sort row and select Ascending or Descending for any field
				that you want to sort.
				<P>
				<DD>Fields are sorted in their order from right to left, the left field being the
				primary sort key.
				<P>To re-order the fields, click and drag the column header.
				<DD><B>4. </B>Uncheck the Show <I>check box</I> for any field you want to hide in
				the Query.
				<P>
				<DD>Fields can be used in queries that do not appear in the query output.
				<P>
				<DD><B>5. </B>Enter any <I>expression</I> in the criteria cell for that field. Click
				Save in the Query Design toolbar. Enter the name of the <I>form</I> in the Query
				<U>N</U>ame <I>text box</I> of the Save As dialog box, then choose OK.
			</DL>
			<P>See the <I>Criteria</I> sections of this part for examples of criteria. When you
			run a query, the data you see reflects the data that conforms to your query at runtime.
			You can update, delete, or append the data in a result set for most queries. (See
			&quot;Updatable Queries.&quot;)
			<H2><FONT COLOR="#000077"><B>Query: Create with Wizard</B></FONT></H2>
			<P>The Query Wizard is easier for novices to create queries but provides less flexibility
			than Query <I>Design View.</I> (See &quot;Query: Create with Design View.&quot;)
			<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 the <U>N</U>ew
				button and choose Simple Query Wizard.
				<P>
				<DD><B>2. </B>Choose a <I>table</I> or <I>query</I> from the <U>T</U>able/Query drop-down
				box.
				<P>
				<DD><B>3. </B>Double-click any <I>fields</I> you want as part of the query. Repeat
				Steps 2 and 3 for additional tables if necessary; choose <U>N</U>ext.
				<P>
				<DD><B>4. </B>If you want to see every <I>record</I>, click the <U>D</U>etail <I>option
				button</I>.
				<P>
				<DD>If you want to only see summaries of the records such as totals or counts, choose
				the Summary <U>O</U>ption button and choose the types of summaries you want; choose
				OK.
				<P>
				<DD><B><BR>
				5.</B>On the next choices in the wizard choose any additional options for grouping
				your data and naming your query; choose <U>F</U>inish when done. Save and close your
				query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Delete Field</B></FONT></H2>
			<P>To remove a <I>field</I> from a <I>query</I> you need to be in Design View. If
			that field is needed in sorting or <I>criteria</I>, uncheck the Show box in the design
			<I>grid</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In the query or <I>filter</I> design <I>grid</I>, click the black down
				arrow mouse pointer on the gray field selector above the field name and press Delete.
				<P>
				<DD><B>2. </B>To remove all fields, choose <U>E</U>dit, Cle<U>a</U>r Grid.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Description</B></FONT></H2>
			<P>If you want to explain your query in more detail than the field name allows, you
			can use the Description <I>property</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>If you are in the <I>Database window</I>, right-click the query name,
				choose <U>P</U>roperties, and type the text in the Description <I>text box</I>.
				<P>
				<DD><B><BR>
				2</B> If you are in Query <I>Design View</I> and the properties window is not in
				view, right-click the gray background behind the Field List(s) and choose <U>P</U>roperties.
				The Query Properties dialog box opens On the General tab type the text in the Description
				text box.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Editing</B></FONT></H2>
			<P>When you want to edit the design of a query, you need to go into Design View.
			You might want to add more fields, delete fields, change your criteria, or change
			other query properties.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You can edit the data in most queries
			the same way you edit a table. (See &quot;Data: Edit&quot; in the <I>Database</I>
			Essentials part of this book.) 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>On the Query tab of the <I>Database window</I>, select the <I>query</I>
				and choose the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>Select any <I>criteria</I> <I>expression</I> and press the Delete <I>key</I>
				to remove it or type a new expression.
				<P>
				<DD><B>3. </B>Change a <I>field</I> by clicking the drop-down arrow in the field
				box and choosing a different field name.
				<P>
				<DD><B>4. </B>If you want to use the same fields but a different <I>table</I>, add
				the other table with the Show Table button and change the table name in the design
				<I>grid</I> for each field, and then click in the old table Field List and press
				the Delete key.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If the table name row does not appear
			in the Query Design grid, choose <U>V</U>iew, Table <U>N</U>ames. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Query: Format Field</B></FONT></H2>
			<P>Often when you create a calculation <I>field</I> (see &quot;Calculated Fields:
			Queries&quot; in the Calculations part of this book), the data is not formatted the
			way you might want. You might also want to change the format of a field to be different
			than the Format <I>property</I> of the field in the underlying <I>table.</I> (See
			&quot;Data: Format&quot; in the Table and <I>Database</I> Design part of this book.)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In Query <I>Design View</I>, right-click the field you want to format
				and choose <U>P</U>roperties.
				<P>
				<DD><B>2. </B>Click in the Format property <I>text box</I> and choose one of the
				formats from the drop-down list.
				<P>
				<DD>You can also type customized formats in the Format box. Some examples include:
				For a required text character, type <B>@</B> and the required text characters. For
				example, if you want<I> </I>the word <TT>Unknown</TT> displayed when there is nothing
				typed in the field, or <TT>No Value</TT> when two quotes have been typed in the field,
				type <B>@;&quot;Unknown&quot;;&quot;No Value&quot;</B>
				<P>
				<DD>For dates, type <B>mmm</B>, or <B>mmmm</B> to see just the month (abbreviation
				or full name) or <B>yyyy</B> to see the year.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For text you can also type <B>&gt;</B>
			to change the entry to be all caps or <B>&lt;</B> for all lower cases. For a description
			of customized format characters, click in the Format property and press F1. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Query: Hide and Show Columns</B></FONT></H2>
			<P>Sometime you will need to use a <I>field</I> but not want to see it displayed
			in the query datasheet.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>If you are in <I>Datasheet View</I>, right-click the column and choose
				<U>H</U>ide Columns from the shortcut menu.
				<P>
				<DD>To redisplay the column choose F<U>o</U>rmat, <U>U</U>nhide columns, pick the
				column, and choose <U>C</U>lose.
				<P>
				<DD><B>2.</B> If you are in Design View, uncheck the Show box in the design <I>grid</I>.
				Recheck the box to display the column again.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Move Column</B></FONT></H2>
			<P>You can move a column either in the <I>Datasheet View</I> or Query <I>Design View</I>.
			Once you've changed the column order in Datasheet View for a query, you need to change
			it in Datasheet View each time rather than depend on Query Design View.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In either Design View or Datasheet View, move the mouse to the column
				or field header until the mouse pointer becomes a black down arrow. Click and release
				the mouse to select the column.
				<P>
				<DD><B>2. </B>With the mouse pointer still on the column header (it is now a white
				arrow), drag the column to the new location.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Properties</B></FONT></H2>
			<P>There are a number of useful properties that you can set as part of a <I>query</I>.
			These properties help you control the number of <I>records</I> returned in your view
			of the result set, optimize performance, and specify other useful options.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a query in <I>Design View</I>.<BR>
				<BR>
				<B>2. </B>Click a <I>field</I> in the Field row, a Field List in the data environment,
				or elsewhere on the Design View window to select the query.<BR>
				<BR>
				<B>3. </B>Click the Properties button on the toolbar to display the <I>Property</I>