PowerPoint Presentation to Accompany GO! with Microsoft ® Access 2007 Volume 1 Chapter 4

1. PowerPoint Presentation to Accompany GO! with Microsoft® Access 2007 Volume 1 Chapter 4 Designing and Building a Relational Database

2. Objectives • Customize Table Fields • Structure Data Input • Validate Data Input • Create Indexes and Change Sort Order • Create Relationships between Tables • Create Forms and Reports with Related Tables

3. Customize Table Fields • Data types determine type of data stored in a field • Field properties ensure data is consistent and correct • Caption properties make entry more user friendly • Ten different data types • Attachment data type used to store pictures, Office files, and small programs

4. Customize Table Fields

5. Structure Data Input • Lookup data type or lookup field retrieves values • Lookup Wizard fills in correct field property to do a lookup • Combo box shows arrow that lists items • List box displays values without arrow • Row source property specifies table, query, or typed list • Multiple column lookup field enables more than one column of data to be seen

6. Structure Data Input

7. Structure Data Input • Input property mask controls the structure of entered data • Input Mask Wizard: Prebuilt input masks for common formats • Input masks can be tested, modified, and created manually • Placeholder characters can be replaced during data entry • Literal characters are characters that cannot be replaced

8. Structure Data Input

9. Structure Data Input • Figure 4.13

10. Structure Data Input Special meanings are assigned to placeholder characters

11. Validate Data Input • Access provides tools to ensure valid data is placed in tables • Fields can be identified as required • Rules can be written to ensure specified criteria are met • By default, only field that must be entered is the primary key

12. Validate Data Input • If field’s Required property is set to Yes, that field must contain an entry • Allow Zero Length property determines if an entry can have zero characters • Set to No the field is required

13. Validate Data Input

14. Validate Data Input • Validation Rule property method to ensure validity • Validation Text property provides a custom message • Data integrity: Data is entered and stored according to its intended use • Default values save time • Data typically entered into forms • Test validation system using forms

15. Validate Data Input

16. Validate Data Input

17. Create Indexes and Change Sort Order • Data is often searched and sorted • Indexes speed searching and sorting • Indexed property creates a second table • Indexes can slow data entry table updates • By default tables sorted on primary key

18. Create Indexes and Change Sort Order • Databases can be sorted on other fields • These fields should be indexed • Yes (Duplicates OK) creates an index that allows duplicates • Yes (No Duplicates) builds an index where every value is unique • Indexes can be built to ensure a field’s value is unique

19. Create Indexes and Change Sort Order

20. Create Relationships between Tables • Many programs can store data in tables • True power of using a database is its ability to relate tables • Relationships are an association between two tables using a common field • Relationships can be created several ways

21. Create Relationships between Tables • Referential integrity refers to rules used to preserve valid data • Related fields do not need to have same name, just same data type • One-to-one relationship: Common field used to join tables • Used in limited situations

22. Create Relationships between Tables

23. Create Relationships between Tables • Subdatasheet is datasheet nested within another • Displays the records joined to the first table through a relationship • Created in Design view

24. Create Relationships between Tables

25. Create Relationships between Tables • One-to-many relationship is an association between two tables in which the primary key of each record in the primary table corresponds to the value in the matching field of many records in the related table • Common field is typically the first table’s primary key

26. Create Relationships between Tables • In second table the related field is called the foreign key • Foreign keys are always on the “many” side of the relationship

27. Create Relationships between Tables

28. Create Relationships between Tables • Referential Integrity are the rules that you follow to preserve the defined relationships between tables when you add, update, or delete records • Cascading update changes related occurrences of data if table is changed • Cascading delete deletes all matching records in the second table when a record is deleted from the first

29. Create Relationships between Tables • In many-to-many relationships records in either table can match many records in the other table • One-to-many relationship is the most common

30. Create Relationships between Tables

31. Create Forms and Reports with Related Tables • Queries, forms, and reports can access data from related tables in a relational database • Relationship report shows the current layout of the Relationship window • Data can be queried from more than one table

32. Create Forms and Reports with Related Tables • A single form can be used to enter data from more than one table • Subforms are forms inserted within another form • They are often used for tables in one-to-many relationships • Subforms display data from a related table

33. Create Forms and Reports with Related Tables

34. Objectives • Customize Table Fields • Structure Data Input • Validate Data Input • Create Indexes and Change Sort Order • Create Relationships between Tables • Create Forms and Reports with Related Tables