1 / 46

SECTION 2 SKILLS Creating Tables and Relationships

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

asa
Download Presentation

SECTION 2 SKILLS Creating Tables and Relationships

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. Understand Table Design Principles…continued

  4. Understand Table Design Principles…continued

  5. Understand Table Design Principles…continued

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. Validate Field Entries…continued

  19. 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

  20. 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

  21. Create Input Masks…continued

  22. 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

  23. 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

  24. 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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.

  33. 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

  34. 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

  35. 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.

  36. 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

  37. 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

  38. 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).

  39. 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

  40. Create a One-To-One Relationship…continued • Turn on the desired relationship options. • Click Create. • Click the Save button. Edit Relationships dialog box

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

More Related