ch04.htm

Microsoft_Access_97_Quick_Reference.zip

				sheet for the selected object.<BR>
				<BR>
				<B>4. </B>Add your properties or <I>expressions</I>; then close the Property sheet.
			</DL>
			<P>Both the query and field have a Description property for notes or comments. The
			field also has a <I>Caption</I> property that shows as the column header or becomes
			the default <I>label</I> for forms and reports. The Format property on the field
			changes the way data is displayed. (See &quot;Query: Format Field.&quot;) The field's
			<I>Input Mask</I> property works the same as for fields in a table. (See &quot;Input
			Mask: Phone Number and Other Entries&quot; in the Table and Database Design part
			of this book.)
			<H2><FONT COLOR="#000077"><B>Query: Run</B></FONT></H2>
			<P>You can run a query from Query Design View or directly from the <I>Database window</I>.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Be careful when you run action
			queries--they will update or delete data. These queries are indicated with an exclamation
			point (!) as part of their icon in the Database window. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>From the Query tab of the Database window, double-click the query name
				to run it.
				<P>
				<DD><B>2. </B>From the Query Design View, click the Datasheet View button to show
				the results of a select query or preview the results of an <I>action query</I>.
				<P>
				<DD><B>3. </B>From the Query Design View, click the Run button to execute the action
				query. This button functions the same as the Datasheet View button for select queries.
				<P>
				<DD><B>4.</B> From Datasheet View, if you want to requery (perhaps you want to redisplay
				the input box for a parameter query), press Shift+F9.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Show All Fields</B></FONT></H2>
			<P>No matter which <I>fields</I> are used in the <I>grid</I>, a filter shows all
			fields. When a <I>filter</I> is saved as a <I>query</I>, all fields also display.
			When you create a new query through <I>Design View</I> or through a wizard, only
			those fields you use in the grid (and that have the Show box checked) display in
			<I>Datasheet View</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Select the query in the <I>Database window</I> and choose the <U>D</U>esign
				button.
				<P>
				<DD><B>2.</B> If the Properties window is not displayed, right-click the gray background
				behind the Field Lists(s) and the title bar of the query, and choose Properties.
				The Query Properties sheet opens.
				<P>
				<DD><B>3. </B>Change the Output All Values <I>property</I> to Yes.
			</DL>
			<H2><FONT COLOR="#000077"><B>Query: Sort Columns</B></FONT></H2>
			<P>You can apply a sort order to the result set of a query in either <I>Form</I>
			or <I>Datasheet View</I>. You can also sort the result set of a filter or an advanced
			filter/sort.</P>
			<P>The sort order for a query is usually determined by the Sort row of the Query
			Design <I>grid</I>. You can sort the query independent of the <I>property</I>, however,
			with the following steps.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the column(s) or field(s) you want to sort on.
				<P>
				<DD><B>2. </B>Click either the Sort Ascending or Sort Descending buttons on the toolbar.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> If your sort is more complex (with multiple
			non-adjacent fields) choose <U>R</U>ecords, <U>F</U>ilter, <U>A</U>dvanced Filter/Sort
			and create your filter. 
<HR>


			</BLOCKQUOTE>

			<P>The primary sort <I>key</I> is the leftmost sorted column in the <I>Query</I>
			Design grid. A sort order is saved with the datasheet or <I>form</I> and reapplied
			when you open it.</P>
			<P>To remove the sort, go into <I>Design View</I> for the query, click the right
			mouse on the gray background behind the <I>Field</I> List(s) and choose <U>P</U>roperties.
			The Query Properties sheet opens. Delete the text in the <I>Filter</I> By property.
			<H2><FONT COLOR="#000077"><B>SQL Statements: View</B></FONT></H2>
			<P><I>SQL</I> stands for <I>Structured Query Language</I>. This is the underlying
			language that Access uses to create its queries, filters, and select data for reports.
			Most of the time you do not have to see the actual SQL statements because Access
			builds them for you in design grids. In a few cases you have to work with SQL if
			you want to perform some features of Access (see &quot;Tables: Combine with Union
			Query&quot; especially).
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Open a query in Design View.
				<P>
				<DD><B>2. </B>Click the View button on the toolbar and choose S<U>Q</U>L View. The
				SQL window opens where you can copy or edit the text instructions for the query.
			</DL>
			<P>Keywords in the query include the type of query (SELECT, DELETE or, for Append,
			INSERT INTO), source of data (FROM), criteria (WHERE), and sort (ORDER BY). Fields
			are indicated by the name of their table, a period, and their field name. If field
			names include spaces, they are enclosed in square brackets.
			<H2><FONT COLOR="#000077"><B>Subqueries: Create</B></FONT></H2>
			<P>Once in awhile you might want to create a <I>query</I> that uses the results of
			another query to run. One option is to create a query and then use that query as
			the basis for the next query. Another option is to copy the <I>SQL</I> statement
			and place it in a <I>Criteria</I> cell.</P>
			<P>You might need to find the average price for all products and then find all products
			that exceed the average price.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> If you want to have both query and <I>subquery</I> listed in the <I>Database
				window</I>, highlight the first query name in the Database window, click the New
				Object button and choose Query. Create the second query using the first queries fields.
				<P>
				<DD><B>2. </B>If you want to have only one query in the Database window, first create
				the subquery with only one field and one <I>record</I> as an output (create a Totals
				Query with no grouping). Display the subquery in SQL view and copy all text. Create
				the main query and move to the Criteria row under the field. Type any <I>operator</I>
				(such as <B>=</B>, <B>&lt;</B>, <B>&gt;</B>), then type an open parenthesis, paste
				the SQL statement, and then type a close parenthesis.
			</DL>
			<H2><FONT COLOR="#000077"><B>Table Names: Display</B></FONT></H2>
			<P>When you are designing a query with multiple tables, the <I>grid</I> can be confusing
			unless you have table names displayed.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> In Query <I>Design View</I>, choose <U>V</U>iew, Table <U>N</U>ames
				to turn the names on or off.
			</DL>
			<H2><FONT COLOR="#000077"><B>Tables: Combine with Join</B></FONT></H2>
			<P>When you have two tables that are related to each other (for example, if you have
			Customers and Orders tables), you might want to see fields from both tables. In the
			Order table you have a Customer ID but no other customer information. To see the
			customer name and phone, as well as order information, you can <I>join</I> two tables
			together in a query.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>From the <I>Design View</I> of a <I>query</I>, click the Show Table
				button to add additional table(s), double-click the <I>child</I> table(s) you want
				and choose the <U>C</U>lose button.
				<P>
				<DD>Access will use any <I>relationship</I> you created in the Relationships window.
				<P>
				<DD><B>2. </B>To create a relationship between two <I>tables</I>, drag the <I>field</I>
				of the parent tables to the related <I>field</I> of the child tables. A line appears
				between the two tables. Generally, you will be dragging the primary <I>key</I> from
				the parent table (which is in bold) to a non-primary field in the child table.
				<P>
				<DD><B>3.</B> To set the join <I>properties</I>, right-click the line and choose
				_Join Properties.
				<P>
				<DD><B>4. </B>The default is the first choice, which only shows <I>records</I> where
				both joined fields have data. (This is called an <I>Inner Join.</I>)
				<P>
				<DD>Choose the second choice to see matched records and all records from the parent
				table without a match in the child table. (This is called a Left <I>Outer Join.</I>)
				<P>
				<DD>Choose the third choice to see matched records and all records from the child
				table without a match in the parent table. (This is called a Right Outer Join.)
				<P>
				<DD><B>5.</B> Choose OK and finish building the query by adding any fields, sort
				options, and criteria you want.
				<P>
				<DD><B>6.</B> Click the Run button to run the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you design your database relationships
			(see &quot;Relationships Between Tables&quot; in the Table and Database Design part
			of this book ) with <I>referential integrity</I> checked, the Right Outer <I>Join</I>
			should yield the same results as the Inner Join because there should be no &quot;orphan&quot;
			records in the <I>child</I> table. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>To find all records that have no
			match, create a query with both linked fields and choose a Left Outer Join or Right
			Outer Join. Under one of the fields set the criteria to Is <I>Null</I>. You might
			want to create this type of query before you create relationships with existing data
			to see which records you need to delete. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Tables: Combine with Union Query</B></FONT></H2>
			<P>The <I>join</I> <I>query</I> in the &quot;Tables: Combine with Join&quot; task
			combines related <I>records</I> and adds additional <I>fields</I> when you have an
			additional <I>table</I>. In some cases, you do not have related records and yet all
			fields of two tables are identical. This can be the case when you have a current
			table and an historic table. When you combine the two tables you want to see one
			record set below the other. You do this through the Union<I> </I>Query, which must
			be written in <I>SQL</I>. Luckily, you can use the Query <I>Design View</I> and then
			paste the results.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Create a query with the first table. Use field names that are the same
				in both tables. Click the View button and choose SQL View.
				<P>
				<DD><B>2. </B>Select the entire SQL statement if not already selected, and click
				the Copy button.
				<P>
				<DD><B>3. </B>Close the query without saving it. Create a query with the second table
				and the matching fields. Click the Design View button and choose SQL View.
				<P>
				<DD><B>4. </B>Move to the end of the SQL statement and press Enter. Type <B>UNION</B>.
				Press Enter again and click the Paste button to paste the SQL statement from the
				first query.
				<P>
				<DD><B>5. </B>Click the Run button to run the query.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If there are any duplicate records
			(all fields), they do not display in a Union Query. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Tables: Create a Self Join</B></FONT></H2>
			<P>When you have a <I>field</I> in the <I>database</I> that refers to another field
			in the same database, you can create a <I>self-join</I> where you join the database
			to itself to display the information you want. This can occur, for example, when
			you have an employee database where one field is the employee ID of the employee's
			supervisor and the supervisor is in the same database. Instead of the supervisor's
			ID, you want to see the supervisor's name.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In Query <I>Design View</I>, select all the fields you want from the
				table.
				<P>
				<DD><B>2. </B>Click the Show Table button and double-click the same <I>table</I>
				so you have two Field Lists from the same table; click <U>C</U>lose to return to
				the design grid.
				<P>
				<DD><B>3. </B>The second table name is identical to the first table name with the
				number 1 added. To avoid confusion, right-click in the second Field List and enter
				a name in the <I>Alias</I> <I>property</I> to giv