ch02.htm

Microsoft_Access_97_Quick_Reference.zip

<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Hyperlinks: Create Field</B></FONT></H2>
			<P>You can use hyperlinks in forms and datasheets to jump to the location described
			in that <I>hyperlink</I>. Locations can be other objects in Access <I>databases</I>,
			documents created by Word, Excel, or PowerPoint, and documents on the Internet or
			an intranet.</P>
			<P>Access contains a new <I>data type</I> called a hyperlink field. A hyperlink field
			contains the text and numbers that comprise a hyperlink address, which is the <I>path</I>
			to the object, document, or Web page. A hyperlink address can also be an URL (Uniform
			Resource Locator) for an Internet or intranet address. Access recognizes a hyperlink
			address from the entered <I>syntax</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> Open the table in the <I>Design View</I> by selecting it in the <I>Database
				window</I> on the Table tab and clicking the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>In the field list, enter the field name for the new hyperlink field.
				<P>
				<DD><B>3. </B>Tab to the Data Type column and select the Hyperlink data type.
				<P>
				<DD><B><BR>
				4.</B>Click the Close (X) button on the Table Design window and choose <U>Y</U>es
				to save the changes to the table design.
			</DL>
			<H2><FONT COLOR="#000077"><B>Index: Create a Composite Index</B></FONT></H2>
			<P>Access enables you to create indexes based on two or more fields in your table,
			up to a limit of 10 fields. You can specify that a composite <I>index</I> is unique
			and use it as a <I>primary key</I>, or use that composite index to speed up sorting
			or searching through your data. Access does not allow you to index on expressions.
			However, in many cases a single field index will be sufficient. (See &quot;Index:
			Create Based on a Single Field.&quot;) A multiple field index can also make up a
			primary key. (See &quot;Index: Primary Key.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a table name in the Tables tab of the <I>Database window</I>,
				then choose the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>Click the Indexes button on the toolbar.
				<P>
				<DD><B>3. </B>Type an index name in the Index Name column of the Indexes window.
				<P>
				<DD><B>4. </B>In the Field Name column, enter the first field in the index.
				<P>
				<DD><B>5. </B>Add additional fields below that line, without naming another index,
				up to ten fields.
			</DL>
			<P>To remove an index, click the Indexes button again, select the rows making up
			the index and press Delete. When searching or sorting on the non-indexed field, Access
			now takes longer. If you have no index at all in a table, Access orders the records
			in the order you enter them in the table.
			<H2><FONT COLOR="#000077"><B>Index: Create Based on a Single Field</B></FONT></H2>
			<P>You can <I>index</I> a single field to serve as a method for ensuring unique values,
			to sort your data, or to speed up search and retrieval operations.</P>
			<P>When you index a field, you have two options. Yes (No Duplicates) means that you
			will not have any entries that match in more than one <I>record</I>. Yes (Duplicates
			OK) means that entries can match.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a table name in the Tables tab of the <I>Database window</I>,
				then choose the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>Click the field in the table <I>Design View</I> and set the Indexed
				<I>property</I> in the General tab to one of the Yes options.
			</DL>
			<H2><FONT COLOR="#000077"><B>Index: Primary Key</B></FONT></H2>
			<P>A <I>primary key</I> is the index used to uniquely identify records in a table.
			Every table should have one primary index, although other unique indexes can be defined
			(as so-called candidate indexes). Often, the primary index is used to establish a
			<I>relationship</I> with a <I>child</I> table. (See &quot;Relationships Between Tables.&quot;)
			The field that the primary key is related to in the other table is called a <I>foreign
			key</I>. A primary key can contain one or more <I>fields</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a table name in the Tables tab of the <I>Database window</I>,
				then choose the <U>D</U>esign button.<BR>
				<BR>
				<B>2. </B>Click the field in the Table <I>Design View</I>. If you want more than
				one field to make up the primary key, hold down Ctrl and click the field selectors
				of the other fields.<BR>
				<BR>
				<B>3. </B>Click the Primary Key button on the toolbar to make the selected field(s)
				the primary key.
			</DL>
			<P>To remove a single field primary key, with that field selected in the Table Design
			View, click the Primary Key button on the toolbar again, or delete the index from
			the Indexes window.
			<H2><FONT COLOR="#000077"><B>Index: Set Index Properties</B></FONT></H2>
			<P><I>Indexes</I> are listings of values or <I>expressions</I> in a <I>field</I>
			or combination of fields. An index in a <I>database</I> operates just like the index
			in a book. Indexes point to where something is located.</P>
			<P>Indexes are particularly valuable in a number of database operations. They speed
			up finding and sorting information when you perform those operations later and also
			change the sorted view of your table. Indexes are necessary to match the data in
			a field of one <I>table</I> to a field in another, and thus provide the means for
			relating one table to another.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a table name in the Tables tab of the <I>Database window</I>,
				then choose the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>Click the Indexes button on the toolbar.
			</DL>
			<P>In the Indexes window you can set index <I>properties</I>: the Index Name (which
			by default takes the field name but can be changed), the Sort Order, and whether
			the index is Primary, Unique, or Ignores Nulls. The Ignore Nulls option makes the
			index smaller and speeds up searching records.
			<H2><FONT COLOR="#000077"><B>Input Mask: Phone Number and Other Entries</B></FONT></H2>
			<P>As you enter information in Text and Date data type <I>fields</I>, you might want
			certain symbols to appear. For example, a phone number has parentheses and a dash.
			You can manually type these symbols in each text field or you can create an <I>input
			mask</I> to automatically do the job. An input mask can also verify each character
			as you type it. To change the display of an entry after you type the entry and move
			out of the field, you can also format the field. (See &quot;Data: Format.&quot;)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT> The input mask wizard gives examples of
			the most common input masks you might want. These include phone number, social security
			number, long zip codes, passwords, and date and time values. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Make sure your other field <I>properties</I>
			(such as Format, Default Value, Validation Rule, and Required) do not conflict with
			your input mask. 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a <I>table</I> name in the Tables tab of the <I>Database window</I>,
				then choose the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>Click the field and click in the input mask <I>property</I> at the
				bottom of the Table Design window.
				<P>
				<DD><B>3. </B>If you want to use a predefined input mask, click the Build button
				to the right of the <I>text box</I> and choose one of the samples in the Input Mask
				Wizard; choose the <U>N</U>ext button.
				<P>
				<DD><B>4. </B>On the next two steps of the Input Mask Wizard, choose the placeholder
				character that you want to appear as the user types each character and whether you
				want to store the symbols with the table. Choose the <U>F</U>inish button when done.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>It is generally better not to include
			symbols with the table because the entries will be shorter (and thus take up less
			room in your database). However, if you will be exporting this data to a spreadsheet
			or other database, you might want to include the input mask symbols. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You can also type Input Mask characters
			directly in the property box in Table <I>Design View</I>. For a description of the
			acceptable characters, click in the Input Mask box and press F1. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Lookup Column: Create with Wizard</B></FONT></H2>
			<P>There are many instances when you might want to look up information to place in
			a field. This is especially true when you have codes representing values. Instead
			of trying to remember the codes, you can create a <I>lookup column</I> that enables
			you to choose something like the employee name rather than remember their employee
			identification number.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> Click a table name in the Tables tab of the Database window, then choose
				the <U>D</U>esign button.
				<P>
				<DD><B>2. </B>If necessary, type the name for the field or go to an existing field.
				Choose Lookup Wizard as the <I>data type</I>.
				<P>
				<DD><B>3. </B>If the data is from another table, identify that you want to use an
				existing table in the first step of the wizard and choose the table in the second
				step.
				<P>
				<DD><B>4.</B> In the third and fourth step of the Lookup Wizard <I>dialog box</I>,
				double-click the fields that you want to appear in the lookup list and choose whether
				you want to hide the <I>key</I> column (usually an ID column).
				<P>
				<DD><B>5. </B>Give the column a name in the last step of the Wizard and choose <U>F</U>inish.
				Access will prompt you to save the table.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The first step of the Lookup Wizard
			also asks you if you want to type the values rather than use an existing table. It
			is generally a better idea to use a table because you can use it for more than one
			combo or list box. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Lookup Column: Properties</B></FONT></H2>
			<P>After you create a <I>lookup column</I> (see &quot;Lookup Column: Create with
			Wizard&quot;), you might want to change or verify the lookup properties for the field.</P>
			<P>The Lookup Column properties identify the source and organization for your drop-down
			menu. One important lookup <I>property</I> is the Row Source, which can be a <I>query</I>
			or <I>SQL</I> statement. You can edit the SQL statement by clicking the build button
			(...) to the right of the Row Source text box and then manipulate the query <I>builder</I>
			just like a normal query. (See &quot;Query: Create with Design View&quot; in the
			Queries and Filters part of this book.)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click a table name in the Tables tab of the Database window, then choose
				the <U>D</U>esign button.<BR>
				<BR>
				<B>2. </B>Choose the lookup field and click the Lookup tab in the Field Properties
				section of the Table Design window.<BR>
				<BR>
				<B>3. </B>The first property--Display Control--is usually set to <I>Combo Box</I>.
				This enables you to choose a drop-down arrow or type in the value. If you choose
				<I>List Box</I>, you only choose from the list. If you choose <I>Text Box</I>, you
				remove the lookup portion of the field and only type in the value in the field.<BR>
				<BR>
				<B>4. </B>If you told the Lookup Wizard to use an existing table or query, the second
				property--Row Source Type--is Table/Query and the third property will be the name
				of a query or an SQL statement that you can edit by clicking the build button (...)
				on the right. If you typed a list of values in the Lookup Wizard, the values that
				you can edit appear in this area.<BR>
				<BR>
				<B>5. </B>The <I>Bound</I> Column stores in the table the value from the specified
				column in Row Source. You might need to change the Column Widths property so you
				can see the entire columns from your Row Source.
				<P>
				<DD><B>6. </B>Click the Close (X) button on the Table Design window and choose <U>Y</U>es
				to save the changes to the table design.
			</DL>
			<H2><FONT COLOR="#000077"><B>Relationships Between Tables</B></FONT></H2>
			<P>When you define a <I>relationship</I> between two<I> tables</I>, you match the
			values in one table to values in another table. In order to create a relationship,
			one or both of the tables requires that the values used in the match be unique. Normally
			an <I>index</I> (usually the <I>primary key</I>) in the controlling or parent table
			is used, and a <I>field</I> (called the <I>foreign key</I>) in the <I>child</I> table
			is matched.</P>
			<P>In addition to defining a relationship, you set <I>referential integrity</I> rules