Include the necessary data Design for now and the future Store data in its smallest parts Add calculated fields too table Design to accommodate date arithmetic Link tables using common fields 5 Include Necessary Data ; Determine what data is necessary ; Create a rough draft of reports that may be needed ; Create tables based on fields necessary for reports 6 Design for Now and the Future ; Organizations evolve over time - Anticipate future needs of the organization - Build flexibility into system to satisfy future needs Store Data in Smallest Possible Pieces ; Creating a name field with the entire name in it violates good database design and reduces the usefulness of the data ; Divide data into the smallest pieces that you're going to need to access - Example: Prefix, Firestone, Lasted, Suffix ; Provide flexibility for the user 8 Calculated Fields in a Table ; Produce a value from an expression or function that references one or more existing fields ; Access 2010 allows the user to store calculated fields - Can be a benefit or a potential problem - Exercise caution when using calculated (derived) 9 Design to Accommodate Date Arithmetic ; Calculated fields can also create date/time data ; Plenty of examples available for using date/time date arithmetic 10 ; Tables may be Joined based on a common field ; Join lines are created - Manually by the user, or - Automatically by Access when two fields in separate tables share the same name between two related tables ; Avoid Data redundancy errors - The unnecessary storage of duplicate data in two or more tables Copyright 0 2013 Pearson Education, Inc.
Publishing as Prentice Hall. 1 Creating Tables ; Create fields in Design View ; Import data from another database or application - Examples: Excel spreadsheets or Word text files ; Enter data directly into rows in Dataset view 12 Creating Fields in Tables ; Field names should be meaningful ; Rules for naming fields: - Length can be up to 64 characters - Can include letters, numbers and spaces - Access uses Camellias notation ; Use uppercase letters for each first letter of each new word ; Example: Products 13 Field Data Types ; Every field has a data type ; Determines: - The operations that can be performed on that data ; Access recognizes 10 data types 14 Access Data Types Number Text Memo Date/Time Currency Yes/No OLE Outnumber Hyperlink Attachment 15 Foreign Key Review A field in one table that is also a primary key of another table Speaker's is the primary key of the Speakers Concessionaires table (duplicates are allowed) 16 Using Table Views Dataset View Active record 17 ; Design View ; Pivotal ; Pivotal 18 Work with Field Properties Field property Text data type Number data type Caption property Validation rule 19 Access Field Properties Field Size Format Input Mask Caption Default Value Validation Rule Validation Text 20 Access Field Properties (continued) Required Allow Zero Length IndexedExpression Result Type 21 Understanding Table Relationships ; Efficiently combine data from related ; Create queries, forms, and Establishing Referential Integrity ; Edit Relationships dialog box ; Select Enforce Referential Integrity checkbook Set Cascade Options ; Cascade Update Related Fields ; Cascade Delete Related Records Click Enforce Referential Integrity Click Cascade Update in case the primary key changes Click Cascade Delete with caution 24 Indexing to Retrieve Data Quickly ; Provides quick sorting based on the primary key ; Provides quick retrieval of data based on the primary key 25 Options on External Data Tab Import & Link Export Web Linked Lists Click Excel to import spreadsheet data External Data Tab 26 Import Data from Excel Click Browse to find a spreadsheet Decide what you want to do with the data 27 (continued) .
