460 likes | 561 Views
SECTION 2 SKILLS Creating Tables and Relationships. 2.1 Understand Table Design Principles 2.2 Create a New Database 2.2 Create a Table 2.3 Create a Table in Design View 2.3 Set the Primary Key CHECKPOINT 1 2.4 Modify the Field Size Property 2.4 Modify the Caption Property
E N D
SECTION 2 SKILLSCreating Tables and Relationships 2.1 Understand Table Design Principles 2.2 Create a New Database 2.2 Create a Table 2.3 Create a Table in Design View 2.3 Set the Primary Key CHECKPOINT 1 2.4 Modify the Field Size Property 2.4 Modify the Caption Property 2.4 Modify the Default Value Property 2.5 Validate Field Entries 2.6 Create Input Masks 2.6 Format a Field 2.7 Create a Lookup List 2.8 Insert, Move, and Delete Fields 2.8 Insert a Total CHECKPOINT 2 2.9 Understand Relationships 2.9 Use the Relationships Window 2.10 Create a One-to-Many Relationship 2.11 Create a One-to-One Relationship 2.12 Print a Relationships Report 2.12 Delete a Relationship 2.12 Display Records in a Subdatasheet CHECKPOINT 3
Understand Table Design Principles To design a new table: • Analyze the required data elements. • Break down the data into the smallest unit. • Create a field name. • Assign each field an appropriate data type. • Identify the field suitable for the primary key. • Assign a name for the table. sample data
Create a New Database To create a new database: • Start Access. • Click the Browse button. • Navigate to the desired drive and/or folder. • Type the file name in the File Name text box. • Click OK. • Click the Create button. Create button File Name text box
Create a Table To create a table: • Click the desired data type in the Add & Delete group in the Table Tools Fields tab. • Type the field name and press Enter. steps continued on next slide… Add & Delete group field name
Create a Table…continued • Click the desired field data type in the drop-down list. • Type the field name and press Enter. • Repeat Steps 3-4 for the remaining columns. • Click the Save button on the Quick Access toolbar. • Type a table name at the Save As dialog box. • Press Enter or click OK. field data type drop-down list table name
Create a Table…continued To type data in the fields: • Click in any empty cell below a column heading to close the Click to Add column. • Click in the empty cell. • Type the desired text. • Press Tab or Enter to move to the next field. • Continue typing the data for the remainder of the fields. typed data
Create a Table…continued To switch to Design view: • Click the View button in the Views group of the Home tab. View button Design view
Create a Table in Design View To create a table in Design view: • Click the Create tab. • Click the Table Design button in the Tables group. • Type the field names, change the data types, add descriptions, or modify other field properties as desired. • Assign a primary key. • Click the Save button in the Quick Access toolbar. • Type a table name. • Click OK. Table Design button field in Design view
Set the Primary Key To assign a primary key: • Open the table in Design view. • Make the desired primary key field active. • Click the Primary Key button in the Tools group in the Table Tools Design tab. • Click the Save button in the Quick Access toolbar. Primary Key button
CHECKPOINT 1 • A field name can be how many characters long? • 24 • 36 • 48 • 64 • In addition to creating a table in datasheet view, a table can be created in this view. • Edit • Print Preview • Design • Structure Answer Answer Next Question Next Question • This is the name of the field that Access creates automatically in new tables. • AB • ID • IA • IB • The primary field is indicated in Design view by this icon. • plus sign • minus sign • key • circle Answer Answer Next Question Next Slide
Modify the Field Size Property To set the field size: • Open the table in Design view. • Click in the desired field row. • Select the current value in the Field Size property box. • Type a maximum number of characters for the field. • Click the Save button in the Quick Access toolbar. Field Size property box
Modify the Caption Property To add a caption: • Open the table in Design view. • Click in the desired field row. • Click in the Caption property box. • Type the desired field label. • Click the Save button in the Quick Access toolbar. Caption property box
Modify the Default Value Property To set a default value: • Open the table in Design view. • Click in the desired field row. • Click or select the current entry in the Default Value property box. • Type the default value. • Click the Save button in the Quick Access toolbar. Default Value property box
Validate Field Entries To create a validation rule: • Open the table in Design view. • Click in the desired field row. • Click in the Validation Rule property box. • Type the statement. • Click in the Validation Text property box. • Type the error message. • Click the Save button in the Quick Access toolbar. Validation Rule property box Validation Text property box
Create Input Masks To use the Input Mask Wizard: • Open the table in Design view. • Click in the desired field row. • Click in the Input Mask property box. • Click the Build button. • Click the input mask you want to create. • Click Next. steps continued on next slide… Build button Input Mask Wizard
Create Input Masks…continued • Select the placeholder character. • Click Next. • Click Finish at the last wizard dialog box. • Click the Save button in the Quick Access toolbar. placeholder character
Format a Field To format a field: • Open the table in Design view. • Click in the desired field row. • Click in the Format property box. • Click the down-pointing arrow at the end of the property box. • Click the desired option at the drop-down list. • Click the Save button in the Quick Access toolbar. Format property box
Create a Lookup List To create a list of values using the Lookup Wizard: • Open the table in Design view. • Type or click the field name. • Press Enter. • Change the data type to Lookup Wizard. • Click the I will type in the values that I want option. • Click Next. steps continued on next slide… Lookup Wizard dialog box
Create a Lookup List…continued • Type the field values in the Col1 column and click Next. • Click Finish at the last wizard dialog box. • Click the Save button in the Quick Access toolbar. field values
Create a Lookup List…continued To change the Limit To List property: • Click the Lookup tab in the Field Properties section. • Click in the Limit To List property box. • Click the down-pointing arrow that appears. • Click Yes. • Click the Save button in the Quick Access toolbar. Limit To List property box
Create a Lookup List…continued To select from a list of values in Datasheet view: • Click the View button to switch to Datasheet view. • Click in the column. • Click the down-pointing arrow that appears. • Click the desired selection in the drop-down list. list of values
Insert, Move, and Delete Fields To insert a field in Design view: • Open the table in Design view. • Make the desired field active. • Click the Insert Rows button in the Tools group in the Table Tools Design tab. • Click in the Field Name area of the new row and type the field name. • Change the data type, description, or field properties as desired. • Click the Save button in the Quick Access toolbar. Insert Rows button
Insert, Move, and Delete Fields…continued To move a field in Design view: • Open the table in Design view. • Select the field using the field selector bar. • With the pointer in the field selector bar, drag the field to the desired position. • Click the Save button in the Quick Access toolbar. move pointer
Insert, Move, and Delete Fields…continued To delete a field in Design view: • Open the table in Design view. • Make the desired field active. • Click the Delete Rows button in the Tools group in the Table Tools Design tab. • Click Yes at the message box. • Click the Save button in the Quick Access toolbar. Delete Rows button message box
Insert a Total To insert a total: • Open the table in Datasheet view. • Click the Totals button in the Records group in the Home tab. • Click in the Total row in the desired field. • Click the down-pointing arrow. • Click the desired function. • Click the Save button in the Quick Access toolbar. functions
CHECKPOINT 2 • This is the default value for the field size of a Text field. • 55 • 155 • 255 • 355 • This displays a pattern indicating how data is to be entered into the field. • data mask • input mask • enter mask • text mask Answer Answer Next Question Next Question • This property stores descriptive titles for fields. • Validation Text • Default Value • Field Size • Caption • When you insert rows using the Insert Rows button, where are new rows inserted in relation to the active field? • above • below • at the beginning • at the end Answer Answer Next Question Next Slide
Understand Relationships • Access is referred to as a relational database management system. • A relational database is one in which tables have been joined. • When two or more tables have been joined to create a relationship, you can look up or create reports from multiple tables as if they were one table. • In most cases, tables are joined by a common field that exists in both tables. • When designing a new database, consider if two tables need to be joined and make sure to include a common field in each table so that you can create the relationship after the tables have been created.
Use the Relationships Window To arrange tables in the Relationships Window: • Click the Database Tools tab. • Click the Relationships button in the Relationships group. • Add the required tables to the window at the Show Table dialog box. • Close the Show Table dialog box. • Resize and move the field list boxes as necessary. • Save the layout. Show Table dialog box
Use the Relationships Window…continued To resize a field list box: • Position the mouse pointer on the bottom border of the field list box until the pointer changes to an up- and down-pointing arrow. • Drag the bottom border down until the vertical scroll bar disappears. resize pointer
Create a One-To-Many Relationship • When two tables are joined in a relationship, one table is called the primary table, and the other table is called the related table. • A one-to-many relationship means that one table in the relationship contains one unique record in the field used to join the tables (this table will be the primary table), while the other table can have several records with a matching field value in the joined field (this table will be the related table). • In this type of relationship, the common field in the primary table is also the primary key field. • In the related table the common field is not the primary key and is known as the foreign key field.
Create a One-To-Many Relationship…continued To create a one-to-many relationship: • Open the Relationships window. • If necessary, add the desired tables to the window. • Close the Show Table dialog box. • Drag the common field name from the primary table field list box to the related table field list box. steps continued on next slide… Relationships window
Create a One-To-Many Relationship…continued • Click Enforce Referential Integrity at the Edit Relationships dialog box. • Click Create. • Click the Save button. Edit Relationships dialog box join line
Create a One-To-One Relationship • A one-to-one relationship exists when both the primary table and the related table contain only one record with a matching field value in the common field. • In this relationship the common field used to join the tables is the primary key in each table. • For example, the Employees table would contain only one record for each employee. The Benefits table would also contain only one record for each employee. If these tables are joined on the common EmployeeIDfield, a one-to-one relationship would be created. • In this type of relationship, consider the primary table to be the table with the fields (such as the employee names) that describe the identity of each ID number (describe for whom the employee number was created).
Create a One-To-One Relationship…continued To create a one-to-one relationship: • Open the Relationships window. • If necessary, add the desired tables to the window. • Close the Show Table dialog box. • Drag the common field name from the primary table field list box to the related table field list box. steps continued on next slide… common field
Create a One-To-One Relationship…continued • Turn on the desired relationship options. • Click Create. • Click the Save button. Edit Relationships dialog box
Create a One-To-One Relationship…continued To edit a relationship: • Open the Relationships window. • Position the mouse pointer on the black join line between the field list boxes. • Right-click. • Click Edit Relationship at the shortcut menu. shortcut menu join line
Print a Relationships Report To print a relationships report: • Open the Relationships window. • Click the Relationship Report button in the Tools group of the Relationship Tools Design tab. • Click the Print button. • Click OK. • Click the Close Print Preview button. Print button
Delete a Relationship To delete a relationship: • Open the Relationships window. • Right-click the join line between the tables. • Click the Delete option from the shortcut menu. • Click Yes at the confirmation message. shortcut menu confirmation message
Display Records in a Subdatasheet To display records in a subdatasheet: • Open the primary table in Datasheet view. • Click the plus symbol next to the record for which you want to view related records. subdatasheet
CHECKPOINT 3 • This is the most common type of relationship created between Access tables. • one-to-one • one-to-many • many-to-one • many-to-many • This symbol appears next to the related table, indicating the many side of a relationship. • plus • minus • asterisk • infinity Answer Answer Next Question Next Question • Always be careful to drag the common field starting from this table. • smallest • largest • primary • secondary • In this type of relationship, the common field used to join the tables is the primary key in each table. • one-to-one • one-to-many • many-to-one • many-to-many Answer Answer Next Question Next Slide