ch02.htm

Microsoft_Access_97_Quick_Reference.zip

			in the relationship <I>dialog box</I>. When you enforce referential integrity, you
			say that you do not want any orphan records in the child table. Orphans occur when
			no records are matched to the parent table. If you choose <I>Cascade U_pdate</I>
			Related <I>Records</I>, whenever you change the ID field in the parent table, the
			field in all corresponding records change in the child table. If you choose <I>Cascade
			</I><CITE>D</CITE><I>elete</I> Related Records, you will delete any children records
			when you delete the parent <I>record</I>. If you choose neither while enforcing referential
			integrity, you will be unable to update the ID field or delete the record when child
			records exist.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>With the <I>Database window</I> showing, click the Relationships button
				on the toolbar to open the Relationship window.
				<P>
				<DD><B>2. </B>Click and drag a relationship between a field from the parent table
				and the field in the child table.
				<P>
				<DD><B>3. </B>The Relationships <I>dialog box</I> opens. In the lower half of the
				window, choose whether you want to enforce referential integrity and how the child
				table will be updated.
				<P>
				<DD><B>4. </B>Finish creating the relationship by choosing OK and closing the relationship
				window.
			</DL>
			<P>The relationship is represented by a line between the two tables. You can select
			a relationship and press the Delete key to remove it. You can also right-click the
			line to view the shortcut menu, and select Edit <U>R</U>elationships to open the
			Relationships dialog box. A command button on the Relationships dialog box is <I>join</I>
			type. You can choose this to set the default join type for queries. (See &quot;Tables:
			Combine with Join&quot; in the Queries and Filters part of this book.)
			<H2><FONT COLOR="#000077"><B>Status Bar: Display User Message</B></FONT></H2>
			<P>The description <I>property</I> of a field appears on the <I>status bar</I> when
			you are in <I>Datasheet View</I> or <I>Form View</I>. The description becomes the
			default for the Status Bar property on a form that you can modify. (See &quot;Controls:
			Properties Change&quot; in the Forms and Reports 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.
				<P>
				<DD><B>2. </B>Choose the field, move to the Description column and type what you
				want to appear in the status bar.
				<P>
				<DD><B><BR>
				3.</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>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If the status bar does not appear
			on your screen, choose <U>T</U>ools, <U>O</U>ptions, View tab, and check the Status
			Bar <I>check box</I> in the Show section. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Table: Create by Table Wizard</B></FONT></H2>
			<P>The Table Wizard is a fast way of creating tables. It lets you structure tables
			based on fields in existing tables, create rudimentary table relationships, and specify
			a <I>primary key</I>.</P>
			<P>If you have existing data, you can also import or link the information to create
			a table. (See &quot;Import Data&quot; in the File Management part of this book.)
			You can also create a table by going directly to design window (see &quot;Table:
			Create in Design View&quot;) or by working in Datasheet View (see &quot;Table: Create
			in Datasheet View&quot;).
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD>1. Click the Tables tab in the Database window, then choose <U>N</U>ew and then
				select Table Wizard in the New Table <I>dialog box</I>, then choose OK.
				<P>
				<DD>2. Click the Business or Personal <I>option button</I> to view a set of sample
				<I>tables</I>.
				<P>
				<DD>3. Select the table(s) you want to view <I>fields</I> from in the Sample Tables
				<I>list box</I>; then move the fields of interest from the Sample Fields list box
				to the Fields in My New Table list box; choose <U>N</U>ext.
				<P>
				<DD>Use the Rename Field button to rename any selected field you add. Access uses
				the same <I>data type</I> for your fields when you rename a field.
				<P>
				<DD>4. Enter a name for the table in the <I>text box</I>; select either Yes for the
				wizard to set a <I>primary key</I>, or No if you will set the primary key; choose
				<U>N</U>ext.
				<P>
				<DD>5. Select any desired <I>relationships</I> in the My New &lt;Tablename&gt; Table
				Is list box; then choose the <U>N</U>ext button.
				<P>
				<DD>6. Select from one of the following: Modify the Table Design; Enter Data Directly
				in the Table; or Enter Data Into the Table From a <I>Form</I> that the Wizard Creates
				for Me. Choose <U>F</U>inish.
			</DL>
			<P>Access creates the new table and saves it to disk. If you select to modify the
			table design, you view the Design window (see also &quot;Table: Create in Design
			View&quot;). For the Enter Data Directly selection in the last step, you see a Datasheet
			window. For the form selection, a form is created for you.
			<H2><FONT COLOR="#000077"><B>Table: Create in Datasheet View</B></FONT></H2>
			<P>The datasheet method is a very fast method for creating tables, but is limited
			in its capabilities. It is best used for small tables where you will add features
			later to the table design. It does not create table relationships, nor does it provide
			for data <I>validation</I> or other table properties.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the Tables tab in the <I>Database window</I>, then choose the
				<U>N</U>ew button.
				<P>
				<DD><B>2. </B>Select <I>Datasheet View</I> in the New Table <I>dialog box</I>.
				<P>
				<DD>A datasheet with 20 columns and 30 rows appears with default field names.
				<P>
				<DD><B>3. </B>Rename the column headings by double-clicking them and entering your
				<I>field</I> name(s); press Enter or click another column or value in the datasheet.
				<P>
				<DD><B>4. </B>Enter data into the datasheet; each column is a field, each row is
				a <I>record</I>.
				<P>
				<DD><B>5. </B>Click Save on the Table Datasheet toolbar. Enter the name of the <I>table</I>
				in the Table Name <I>text box</I> in the Save As dialog box, then choose OK.
				<P>
				<DD>An alert box is posted asking you if Access can create a <I>primary key</I>;
				click <U>Y</U>es if you haven't created a field with unique values that can identify
				each row of your data-sheet (records in the table); click <U>N</U>o if you have created
				such a field.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Use a consistent style of data within
			a column for dates, times, numbers, and so on, so that Access can create a <I>data
			type</I> and display format based on the values it sees you enter. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>If you need more than 20 columns,
			click a column to the right of your new field, then select the <U>C</U>olumn command
			from the <U>I</U>nsert menu. Access will automatically add rows after the 30<SUP>th</SUP>
			record. 
<HR>


			</BLOCKQUOTE>

			<P>Access creates the new table and saves it to disk. When you have Access create
			the primary key, it creates an <I>AutoNumber</I> field that has sequential numbers
			entered into it.
			<H2><FONT COLOR="#000077"><B>Table: Create in Design View</B></FONT></H2>
			<P>A convenient place to create the structure of your database tables is Table <I>Design
			View</I>. This is where you add and remove fields, and it serves as a convenient
			venue for getting an overview of the properties associated with your fields and table.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the Tables tab in the <I>Database window</I>, then choose <U>N</U>ew.
				Select Design View in the New Table <I>dialog box</I>, then choose OK.
				<P>
				<DD><B>2. </B>Enter a name for a <I>field</I> in the Field Name column, then Tab
				and enter the <I>data type</I> in the Data Type column.
				<P>
				<DD><B>3. </B>Enter into the Description column the information you want displayed
				in the <I>status bar</I> when the <I>insertion point</I> is in that field in the
				table. Enter Field Size (number of characters), Format and <I>Input Mask</I> (display
				and allowable characters), <I>Caption</I> (for the <I>Datasheet view</I>), Default
				Values, <I>Validation</I> rules, and other <I>properties</I> in the General <I>section</I>.
				<P>
				<DD><B>4.</B> Click the next blank line of the field <I>grid</I> and create the next
				field in your database; then repeat Step 5. To insert a field between two other fields,
				click the Insert Rows button on the Table Design toolbar.
				<P>
				<DD><B>5. </B>To select the field you want to use to create a <I>primary key</I>,
				click the field selector to the right of the field name. Or, select multiple fields
				for a compound primary key by holding Ctrl and clicking each field selector; then
				click the <I>Primary Key</I> button on the toolbar.
				<P>
				<DD><B>6. </B>Click <U>S</U>ave on the Table Design toolbar; enter a name for the
				table in the Table Name <I>text box</I> of the Save Table dialog box and choose OK.
			</DL>
			<P>Access creates the new table and saves it to disk.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>You don't have to assign a primary
			key, but it is recom-mended. Make sure that the order of a compound primary key is
			correct. You can change the order by clicking the Indexes button on the toolbar and
			reordering the field names in the <I>index</I> that comprises the Primary Key. However,
			a compound primary key is used infrequently compared to a single field primary key.
			
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Table: Modify Design</B></FONT></H2>
			<P>To modify a <I>table</I>, you must select that table in the <I>Database window</I>
			and open the table in the Table <I>Design View</I>. In this view you can add or remove
			<I>fields</I>; change field names; change a field's <I>data type</I>; and add, modify,
			or delete descriptions, field <I>properties</I>, and table rules.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Pay particular attention to modifying
			the data type of an existing field. When you change data types, there is the potential
			for data loss due to data type mismatch. Your previous field's data may be truncated
			or discarded completely. Once it is gone, it is gone forever. Therefore, it is a
			good idea to make a backup of your database or table before you change the design
			of a table. (See &quot;Backup Data&quot; and &quot;Database Object: Copy&quot; in
			the File Management part of this book.) 
<HR>


			</BLOCKQUOTE>

			<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><BR>
				2.</B>Make your changes or additions in the Table Design View.
				<P>
				<DD><B><BR>
				3.</B>Choose <U>I</U>nsert, <U>R</U>ows to add fields, or <U>E</U>dit, Delete <U>R</U>ows
				to remove fields.
				<P>
				<DD><B>4. </B>Choose <U>V</U>iew, <U>I</U>ndexes to create or modify table indexes,
				or <U>V</U>iew, <U>P</U>roperties to add or alter table properties.
				<P>
				<DD><B>5. </B>Enter any General field properties like Captions, <I>Default Values</I>,
				Format, <I>Input Masks</I>, <I>Validation</I> Rule, Validation Text, Allow <I>Zero
				Length</I>, Required (mandatory data entry), and so on, that you want.
				<P>
				<DD><B>6. </B>After you have finished modifying your table, select <U>F</U>ile, <U>S</U>ave.
				Or, to save the resulting table as a different file, select <U>F</U>ile, Save <U>A</U>s;
				name and locate your table in the file system using the Save As <I>dialog box</I>;
				then choose OK.
			</DL>
			<P>Access saves your table to disk. If you create a new table, that table's name
			appears in the Table tab of the Database window.
			<H2><FONT COLOR="#000077"><B>Table: Properties</B></FONT></H2>
			<P>A number of important <I>table properties</I> can be specified that affect how
			data is stored and accessed. Two different groups of properties can be accessed.
			The first set of properties are object properties that are the same for <I>queries</I>,
			<I>forms</I>, <I>reports</I>, <I>macros</I>, and modules. They include the object
			description, whether you want to hide the object in the database window, and whether
			you want to replicate the object. (See &quot;Replication: Create Replica&quot; in
			the File Management part of this book.)</P>
			<P>The second set of properties you access from i