1 / 39

Designing Structure of Tables: Advanced Techniques

Learn how to design the structure of tables in a new database using advanced techniques such as field properties and data segmentation. Understand the importance of relationships and data redundancy in relational databases.

jalfonso
Download Presentation

Designing Structure of Tables: Advanced Techniques

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. Access 2010 Level 2 Unit 1 Advanced Tables, Relationships, Queries, and Forms Chapter 1 Designing the Structure of Tables

  2. Designing the Structure of Tables Quick Links to Presentation Contents • Design Tables and Fields for a New Database • Restrict Data Entry and Data Display Using Field Properties • CHECKPOINT 1 • Work with Memo Fields • Create an Attachment Field and Attach Files to Records • CHECKPOINT 2

  3. Design Tables and Fields for a New Database • Most of the databases you will use in the workplace will have already been created by database designers. • An introduction to the process involved in creating a new database will help you to understand why objects are organized in a certain way and how they are related to one another. • Creating a new database from scratch involves careful advance planning.

  4. Design Tables and Fields for a New Database…continued • As the designer maps out the tables that will make up the database and the fields that will make up each table, he or she incorporates the following techniques: • Each table is considered an entityand should describe a single person, place, object, event, or other subject. • Data should be segmented until it is in its smallest unit. • Fields that can be calculated by using data in other fields should not be included. • Identify fields that can be used to answer questions from the data. continues on next slide…

  5. Design Tables and Fields for a New Database…continued • Specify a data field in each table that that will uniquely identify eachrecord. • Determine each table’s relationships to other tables and the field you will use to join the two when you create relationships. • Relational databases are built upon the concept that data redundancy should be avoided except for fields that will be used to join tables in a relationship. • The design process may seem time-consuming; however, the time spent to plan and produce a well-designed database saves time later.

  6. Design Tables and Fields for a New Database…continued • Recall from Level 1, Chapter 1 that designers often create a visual representation of the database’s structure in a diagram. diagram of a table structure

  7. Design Tables and Fields for a New Database…continued continues on next slide…

  8. Design Tables and Fields for a New Database…continued

  9. Design Tables and Fields for a New Database…continued • By default, the Field Size property of a text field is set to a width of 255. • Access uses only the amount of space needed for the data that is entered, even when the field size allows for more characters, so you may wonder why youwould ever change the property to a smaller value. • One reason is that changing the Field Size property can allow you to restrict the length of data entered into the field.

  10. Restrict Data Entry and Data Display Using Field Properties • The properties that are available for a field depend on the field’s data type. • Use the options available in the Field Properties section in Design view to place restrictions on data entered into a field and to ensure data is entered and displayed consistently.

  11. Restrict Data Entry and Data Display Using Field Properties…continued To add a caption to an existing field: • Open the table in Design view. • Activate the desired field. • Click in the Caption property box. • Type the descriptive text. • Save the table. Caption property box

  12. Restrict Data Entry and Data Display Using Field Properties…continued To require data in a field: • Open the table in Design view. • Activate the desired field. • Click in the Required property box. • Click the down-pointing arrow. • Click Yes. • Save the table. Required property box

  13. Restrict Data Entry and Data Display Using Field Properties…continued • When you leave a field blank when entering a new record, Access records a null value in the field. • A zero-length field can be used to indicate that a field is not applicable to the current record. This is different than leaving the field blank.

  14. Restrict Data Entry and Data Display Using Field Properties…continued To disallow zero-length strings in a field: • Open the table in Design view. • Activate the desired field. • Click in the Allow Zero Length property box. • Click the down-pointing arrow. • Click No. • Save the table. Allow Zero Length property box

  15. Restrict Data Entry and Data Display Using Field Properties…continued • The Format property controls how data is displayed in a field in adatasheet, query, form, or report. • The formats available to you depend on the field’s data type.

  16. Restrict Data Entry and Data Display Using Field Properties…continued continues on next slide…

  17. Restrict Data Entry and Data Display Using Field Properties…continued

  18. Restrict Data Entry and Data Display Using Field Properties…continued To format a Text field: • Open the table in Design view. • Activate the desired field. • Click in the Format property box. • Type the desired format codes. • Save the table. Format property box

  19. Restrict Data Entry and Data Display Using Field Properties…continued

  20. Restrict Data Entry and Data Display Using Field Properties…continued • You can specify up to four different formats for a numeric field, allowing you to include different options for displaying positive values, negative values, zero values, and null values. • Examine the following custom format code: #,###.00;-#,###.00[Red];0.00;"Unknown"

  21. Restrict Data Entry and Data Display Using Field Properties…continued To format a Number field: • Open the table in Design view. • Activate the desired field. • Click in the Format property box. • Type the desired format codes or select from the predefined list. • Save the table. Format property box

  22. Restrict Data Entry and Data Display Using Field Properties…continued

  23. Restrict Data Entry and Data Display Using Field Properties…continued To format a Date/Time field: • Open the table in Design view. • Activate the desired field. • Click in the Format property box. • Type the desired format codes or select from the predefined list. • Save the table. Format property box

  24. Restrict Data Entry and Data Display Using Field Properties…continued • You can use an input mask when you want to control the type of data that can be entered into a field and the pattern in which it is entered. • Using input masks ensures that data will be entered consistently acrossall records.

  25. Restrict Data Entry and Data Display Using Field Properties…continued

  26. Restrict Data Entry and Data Display Using Field Properties…continued • An input mask can contain up to three sections separated by semicolons. • The following is an example of an input mask to store a four-digit customer identification number with a pound symbol (#) as the placeholder: 0000;;#. • The first section of the input mask, 0000,contains the four required digits ofthe customer identification. Since the mask contains no display characters (such as a hyphen), the second section is left blank. The third section, located after the second semicolon, contains the placeholder character, a pound symbol.

  27. Restrict Data Entry and Data Display Using Field Properties…continued To create a custom input mask: • Open the table in Design view. • Activate the desired field. • Click in the Input Mask property box. • Type the input mask codes. • Save the table. Input Mask property box

  28. Restrict Data Entry and Data Display Using Field Properties…continued • Other field properties that should be considered for data accuracy when designing database tables include the Default Value, Validation Rule, and Validation Text properties. • In Level 1 you learned to add a validation rule using the Validation button in the Field Validation group of the Table Tools Fields tab. In Design view, the Validation Rule and Validation Text properties are located just above the Required property.

  29. CHECKPOINT 1 • Designers often create a visual representation of the database’s structure in this. • object • diagram • chart • table • By default, the Required property is set to this. • on • off • yes • no Answer Answer Next Question Next Question • By default, text fields are set to this width in the Field Size property. • 55 • 155 • 255 • 355 • Using these ensures data is entered consistently in all records. • input masks • output masks • input fields • output fields Answer Answer Next Question Next Slide

  30. Work with Memo Fields To enable rich text formatting in a Memo field: • Open the table in Design view. • Activate the desired field. • Click in the Text Format property box. • Click the down-pointing arrow. • Click the Rich Text option. • Click Yes. • Save the table. Rich Text option

  31. Work with Memo Fields…continued To track changes in a Memo field: • Open the table in Design view. • Activate the desired field. • Click in the Append Only property box. • Click the down-pointing arrow. • Click Yes. • Save the table. Append Only property box

  32. Create an Attachment Field and Attach Files to Records To create an Attachment field: • Open the table in Design view. • Click in the first blank field row. • Type the desired field name. • Click in the Data Type column. • Click the down-pointing arrow. • Click the Attachment option. • Save the table. Attachment option

  33. Create an Attachment Field and Attach Files to Records…continued To attach files to a record: • Open the table in Datasheet view. • Double-click the paper clip in the desired record. • At the Attachments dialog box, click the Add button. • Navigate to the drive and/or folder location. • Double-click the file name. • Click OK. Attachments dialog box

  34. Create an Attachment Field and Attach Files to Records…continued To view the attached file: • Open the table in Datasheet view. • Double-click the paper clip in the desired record. • Double-click the file name. • View the file contents. • Exit the source program. • Click OK. file contents of source program

  35. Create an Attachment Field and Attach Files to Records…continued To edit an attached file: • Open the file attachment. • Make the desired changes. • Click the Save button in the source program. • Exit the source program. • Click OK. • At the Save Attachment dialog box, click Yes. Save Attachment dialog box

  36. Create an Attachment Field and Attach Files to Records…continued • To export a file that is attached to a record and save it in another location, select the file and then click the Save As button in the Attachments dialog box. • To remove a file that is attached to a record, open the Attachments dialog box in the record containing the file attachment, click the name of the file you want to delete, click the Remove button, and then click OK to close the Attachments dialog box.

  37. CHECKPOINT 2 • By default, Access formats a Memo field as this type of text. • ordinary • enhanced • rich • plain • Using this type of field you can store several files in a single field attached to a record. • Append Only • Attachment • Text • Memo Answer Answer Next Question Next Question • The Append Only property for a Memo field is set to this by default. • No • Yes • On • Off • An Attachment field displays with this in Datasheet view. • thumbnail • push pin • paper clip • staple Answer Answer Next Question Next Slide

  38. Designing the Structure of Tables Summary of Presentation Concepts • Design the structure of tables to optimize efficiency and accuracy of data • Select the appropriate field data type based on analysis of source data • Disallow blank field values • Allow or disallow zero-length strings in a field • Create a custom format for text, number, and date fields • Create a custom input mask • Define rich text formatting for a memo field • Store history of changes to a memo field • Define and use an attachment field with multiple attachments

More Related