ch04.htm

Microsoft_Access_97_Quick_Reference.zip

				<P>
				<DD><B>4. </B>Choose <I>Append Query</I> from the Query Type button and enter your
				new table name.
				<P>
				<DD><B>5. </B>Click the Run button to run the query and answer Yes to the prompts.
			</DL>
			<P>This query works because the primary key in a table cannot be duplicated. After
			you view your second table to make sure the information is correct, you can delete
			the first table and rename the second. (See &quot;Database Object: Delete&quot; and
			&quot;Database Object: Rename&quot; in the File Management part of this book.)
			<H2><FONT COLOR="#000077"><B>Filter Data: Advanced Filter</B></FONT></H2>
			<P>The most complex filters you can create enable you to both <I>filter</I> and sort
			<I>records</I> in a single operation for a single <I>table</I> or <I>query</I>. The
			Advanced Filter/Sort window is similar to the Query <I>Design View</I> in construction
			in that you work in a design <I>grid</I> where you specify the criteria used to filter
			your records and the sort order. The Advanced Filter/Sort feature can operate on
			tables or queries, but cannot provide related tables.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a table, query, or <I>form</I> in the <I>Datasheet View</I>, or
				a form in the <I>Form View</I>.
				<P>
				<DD><B>2. </B>Choose <CITE>R</CITE><I>ecord</I>, <U>F</U>ilter, <U>A</U>dvanced Filter/Sort.
				<P>
				<DD><B>3. </B>Enter the <I>criteria</I> and sort you want in the design grid.
				<P>
				<DD><B>4. </B>Click Apply Filter on the toolbar.
			</DL>
			<H2><FONT COLOR="#000077"><B>Filter Data: By Form</B></FONT></H2>
			<P>If you want to select a set of records in a datasheet or a <I>form</I>, the simplest
			way to do this is to set a <I>filter</I>. A filter is a single set of <I>criteria</I>
			that can be applied to your data set. When you apply another filter to a result set
			from a previous filter, you narrow your result set even further.</P>
			<P>Access remembers your last filter in a session and lets you reapply it at any
			time. If you apply a filter to a <I>table</I> or <I>form</I>, Access remembers that
			filter until you apply a new one. Filters applied to <I>queries</I> are not entered
			into the query <I>grid</I>, but can be applied later separately. You can also sort
			filtered <I>records</I>. You might find that filters provide much of the find and
			query capability you need in your work.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a table, query, or form in the <I>Datasheet</I> or <I>Form View</I>.
				<P>
				<DD><B>2. </B>Click the Filter By Form button on the toolbar to open a Filter By
				Form window.
				<P>
				<DD><B>3. </B>Click the <I>field</I> you want to filter by and enter the selection
				criteria that records must match to be returned in a result set.
				<P>
				<DD><B>4. </B>To enter a value to search for, select that value from the list in
				that field; or enter the value manually.
				<P>
				<DD><B>5. </B>To enter a value of a <I>check box</I>, <I>option button</I>, or <I>toggle
				button</I>, click that button. Entering a value in two fields on the same line in
				the Look For tab (see the bottom of the window) requires both values to be matched
				in the result set.
				<P>
				<DD><B>6. </B>To perform a filter based on alternative values, click the OR tab at
				the bottom of the window and enter additional criteria. Click the Apply Filter button
				on the toolbar to perform the selection.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Check boxes display three states:
			checked (on), not checked (off), and mixed state (grayed). Make sure you place a
			check mark into the condition you want. Also, you can select a field based on the
			conditions Is <I>Null</I> or Is Not Null. 
<HR>


			</BLOCKQUOTE>

			<P>You can reapply the filter by clicking the Apply Filter button at a later time.
			You can use the Remove Filter button to remove your current filter.
			<H2><FONT COLOR="#000077"><B>Filter Data: By Selection</B></FONT></H2>
			<P>You can <I>filter</I> the records shown in a <I>form</I>, <I>subform</I>, or datasheet
			by applying a Filter By Selection. When you click the toolbar button, all records
			that match your selected <I>field</I> display. This procedure is very simple to use,
			but more limited than Filter By Form. (See &quot;Filter Data: By Form.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a <I>table</I>, <I>query</I>, or form in the <I>Datasheet View</I>,
				or a form in the <I>Form View</I>.
				<P>
				<DD><B>2. </B>Select the <I>record</I> that has the value you want to limit in a
				form or datasheet.
				<P>
				<DD><B>3. </B>Click the insertion point in the <I>field</I> (this will cause the
				entire field's contents to be the <I>criteria</I> for the filter).
				<P>
				<DD><B>4. </B>Click the Filter by Selection button on the toolbar.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> As mentioned in the preceding Step 3 you
			can click in a field (with no text selected) to have Access use the entire field
			for the filter. You can also select part of a field starting with the first character
			to return records where the value in that field also starts with those characters.
			Another option is to select any value after the first character in a field to return
			all or part of a value in that field with the same characters. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Filter Data: Filter With Shortcut Menu</B></FONT></H2>
			<P>Access lets you enter a filter directly into a <I>field</I> on a field's shortcut
			menu as a filter request.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a <I>table</I>, <I>query</I>, or <I>form</I> in the <I>Datasheet
				View</I>, or a form in the <I>Form View</I>.
				<P>
				<DD><B>2. </B>Right-click a field and enter a value or <I>expression</I> in the <U>F</U>ilter
				For <I>text box</I>; then press Enter.
			</DL>
			<P>By successively filtering, you can narrow your result set. You can press Tab while
			still in the Filter For text box in Step 2 to apply the filter and add a new filter
			criteria as well.
			<H2><FONT COLOR="#000077"><B>Filter Data: Save as Query</B></FONT></H2>
			<P>After working with a <I>filter</I> you might want to use it again under different
			circumstances. You can save the filter as a <I>query</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Create a filter from any of the methods mentioned in the filter tasks
				in this part of the book.
				<P>
				<DD><B>2. </B>Go to the filter design window by choosing <CITE>R</CITE><I>ecord</I>,
				<U>F</U>ilter, <U>A</U>dvanced Filter/Sort.
				<P>
				<DD><B>3. </B>If desired, modify the filter <I>grid</I> and click the Save As Query
				button on the toolbar.
				<P>
				<DD><B>4. </B>Enter the name of the query in the Save As Query <I>dialog box</I>.
			</DL>
			<P>If you want to use the specification from this query in a filter again, go to
			the filter design grid by choosing <U>R</U>ecord, <U>F</U>ilter, <U>A</U>dvanced
			Filter/Sort, click the Load from Query button and double-click the query name.</P>
			<P>This query now appears on the Query tab of the <I>Database window</I>. You can
			use it like any other query. One difference is that all fields are displayed rather
			than just the fields that are in the grid. This is because the Output All Fields
			<I>property</I> is set to Yes.
			<H2><FONT COLOR="#000077"><B>List of Values</B></FONT></H2>
			<P>Sometimes you want to just see a list of all the different values in one <I>field</I>
			with none of the items duplicated. You might want to create a lookup <I>table</I>
			with these values, in which case you could create this query and then turn it into
			a Make-Table Query (see &quot;Action Queries: Make-Table Query&quot;) and use the
			table in a <I>lookup column</I> (see &quot;Lookup Column: Create with Wizard&quot;
			in the Table and Database Design part of this book).
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In the <I>Database window</I>, select the table or query from which
				you want to find the values. Choose <U>Q</U>uery from the New Object button on the
				toolbar.
				<P>
				<DD><B>2. </B>Right-click the mouse button on the query's title bar and choose <U>P</U>roperties
				on the shortcut menu. The Query Properties sheet opens.
				<P>
				<DD><B>3. </B>Change the Unique Values <I>property</I> to Yes.
				<P>
				<DD><B>4. </B>Double-click in the Field List the one field for which you want to
				see values.
				<P>
				<DD><B>5. </B>Close the Property sheet, save, and run the query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query Design: Expand Column</B></FONT></H2>
			<P>At any point during <I>query</I> design you might not have enough room to see
			your <I>field</I> or <I>criteria</I>. You can change the column width temporarily
			to see what you need at the moment, or you can permanently widen the column.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> If you permanently want to see a wider column width, move to the gray
				border above and between two field names. The mouse pointer will be a double-headed
				arrow. Drag the mouse to the right to increase the column width.<BR>
				<BR>
				<B>2. </B>If you want to temporarily see the entire entry, press Shift+F2 to open
				the Zoom window. Choose OK when you are done viewing or editing.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query Parameters: Prompt for Input</B></FONT></H2>
			<P>When you want one query to work as many, consider using a parameter query. For
			example, suppose you need all the same information but want to change the salesperson
			each time you run the query. When you create a <I>parameter</I> query, Access will
			prompt you each time you run the query. This is true even when you open a <I>form</I>
			or <I>report</I> based on this <I>query</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Create the query with all the fields and other criteria you want and
				open the query in <I>Design</I> View.<BR>
				<BR>
				<B>2. </B>Move to the criteria <I>grid</I> under the <I>field</I> you want to check.
				Type an open square bracket, any text you want for the prompt, and a close square
				bracket. For example, type <B>[Enter the salesperson's name:]</B>.<BR>
				<BR>
				<B>3. </B>You might have more than one prompt and need to change the order of the
				prompts or define a <I>data type</I> for the prompt (so Access displays a warning
				if the wrong data is entered in the prompt). Choose <U>Q</U>uery, Para<U>m</U>eters.
				Enter the exact text of your prompt and choose a data type for each prompt.<BR>
				<BR>
				<B>4. </B>Click the Run button. Access will show a <I>dialog box</I> with whatever
				prompt you added. Enter your text.<BR>
				<BR>
				<B>5. </B>Save and close your query.
			</DL>
			<P>Whenever you run the query (including just opening it) Access will display a dialog
			box with the prompt you created.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Enter the keyword <B>Like</B> before
			your prompt if you want to use wildcards. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Query: Add Field</B></FONT></H2>
			<P>When you edit or build a query you might need to insert fields in the appropriate
			place on the <I>grid</I>. Open up any query in Design View to add a <I>field</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>To add a field at the end of the query grid, double-click the field.
				<P>
				<DD><B>2.</B> To add a field between two existing fields, drag the field name from
				the <I>list box</I> onto the right field.
				<P>
				<DD><B>3. </B>To add all fields in the grid, first clear the grid if necessary with
				<U>E</U>dit, Cle<U>a</U>r Grid.
				<P>Double-click the asterisk (*) in the Field List.
				<DD><B>4. </B>If you want to add all fields to the grid and you are going to use
				sort or criteria from any of the fields, double-click the title bar of the list box
				and drag from one of the fields to the grid.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Add Table</B></FONT></H2>
			<P>You can use more than one <I>table</I> in the <I>query</I>. In almost all cases
			the tables need to be related. Open up any query in Design View to add a table.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In Query <I>Design View</I>, click the Show Table button on the toolbar.
				<P>
				<DD><B>2. </B>Double-click each table or query you want to add; choose <U>C</U>lose
				when done.
				<P>
				<DD><B>3. </B>If necessary, draw a <I>join</I> line between the two related fields
				in the <I>Field</I> Lists. Generally, you will be drawing a line between two fields
				with the same or similar names (for example, EmpID and Employee ID).
				<P>
				<DD><B>4. </B>Double-click fields from any of the Field Lists to add them to the
				design <I>grid</I>.