Intermediate MS Access

1. Intermediate MS Access Instructor: Vicki Weidler Assistant:

2. Unit 9: Creating Relational Databases

3. Overview • Normalize tables to reduce data redundancy • Understand the requirements for first, second, third, fourth & fifth normal forms • Set one-to-one, one-to-many, and many-to-many relationships between tables • Implement referential integrity between related tables • Use cascading deletes and updates to prevent orphan records

4. Normalization The process of restructuring data files to: • reduce data to its simplest structure • minimize redundancy • achieve the most efficient and functional yet flexible way to store data

5. First Normal Form (1NF) Eliminates duplicate data… • a table should not contain similar information in several fields; eliminate duplicative columns from the same table or move repeating fields to a related table • a table should not contain fields that can be further broken down into smaller meaningful parts • create separate tables for each group or related data and identify each row with a unique column (the primary key); each table should describe a single entity (object) class Definition 1NF

6. Second Normal Form (2NF) Requires all data in the table apply directly to the subject of the table indicated by the primary key field… • if the table has a single-field primary key and meets the requirements for 1NF, then it also meets the requirements for 2NF • however, if the table has a composite (multiple-field) primary key, then all the fields in the table must be related to and/or dependent on ALL the fields of the composite primary key • to convert a table to 2NF, find all the fields that are related to only part of the composite (multiple-field) primary key, group these fields into another table, then assign a primary key to the new table • another way of looking at it is to remove ALL subsets of duplicate/redundant data that apply to multiple rows of a table and place them in new tables, then create relationships between these new tables and the original tables by using foreign keys Definition 2NF

7. Third Normal Form (3NF) Eliminates fields that can be derived from other fields… • if a table contains fields that do not relate to and are not fully dependent on the primary key and completely describe the object that table represents, then it is not in 3NF • to convert a table to 3NF, delete all the fields that do not relate to the primary key Definition3NF

8. Fourth Normal Form (4NF) Requires that tables not contain fields for two or more independent, multi-varied facts… • this rule requires splitting tables that consist of lists of independent attributes • i.e. in a relation table, job skills and language fluency are independent facts about an employee; therefore, the 3-field relation table must be split into 2 two-field relation tables

9. Fourth Normal Form (4NF)

10. Fifth Normal Form (5NF) Requires the capability to reconstruct the source data from the tables that have met 1NF, 2NF, 3NF and 4NF… • you should be able to regenerate from the database a spreadsheet that contains all the data in the original version • this is an “acid test” for normalization and requires considerable skill in designing queries to join related tables to produce a readable output to compare with the original data

11. Relating Tables Relationshipsused to extract data from several tables at the same time Primary keyunique identifier for each record in a table Foreign keythe primary key in the related table

12. Types of Relationships One-to-One Relationship When one complete record in the primary table is related to just one record in the related table and vice versa; both tables are equally dependent on each other One-to-Many Relationship When one record in the primary table is related to several records in the related table; however, a record in the related table has only one related record in the primary table Many-to-Many Relationship When several records in one table are related to several records in another table. A many-to-many relationship cannot be created directly in MS Access. To relate such tables, you must first create a junction table that contains the primary keys of both tables

13. Referential Integrity Referential Integrity Ensures that changes made to one table will be reflected in the related table Orphan Records When a record is deleted in the primary table without deleting the related records in the other table

14. Referential Integrity Cont. Cascading Deletes When you delete a record from the primary table, cascading deletes automatically deletes all related records from other related tables; this ensures that there are no orphan records Cascading Updates When you change a primary key in a table, the change is updated in all of the related tables

15. Summary • Normalize tables to reduce data redundancy • Understand the requirements for first, second, third, fourth & normal forms • Set one-to-one, one-to-many, and many-to-many relationships between tables • Implement referential integrity between related tables • Use cascading deletes and updates to prevent orphan records

16. Independent Practice Activity

17. Unit 10: Working with Related Tables

18. Overview • Create Lookup lists using the Lookup Wizard & Design View • Modify Lookup field properties • Learn how to change control types • Add data to related tables utilizing a subdatasheet

19. Lookup Wizard The Lookup Wizard creates a field in one table that gets data from a field in another table. This is called a Lookup list field. The Lookup field uses the primary key from the related table to ensure referential integrity.

20. Table Control Types Text Box used to enter data in a table; cell Combo Box drop-down list of values; you can either select a value from the list or enter a value not on the list List Box drop-down list of values; unlike the combo box, you can only select a value from the list, you cannot enter a value that is not on the list

21. Adding Data to Related Tables Subdatasheet • Set of records extracted from a table based on a record in another table • Use to view and enter data in related tables

22. Summary • Create Lookup lists using the Lookup Wizard & Design View • Modify Lookup field properties • Learn how to change control types • Add data to related tables utilizing a subdatasheet

23. Independent Practice Activity

24. Unit 11: Defining Data Entry Rules

25. Overview • Create field input masks using the Input Mask Wizard • Set Required, Allow Zero Length, and Field Size properties • Define validation rules and text for entering data in a field

26. Input Masks Input masks allow you to control the way data is entered in a field • First section contains the input mask • Second section determines whether to store literal characters with the values that are entered • Third section specifies placeholder characters

27. Input Mask Characters

28. Setting Properties

29. Validation Rules You can set conditions on types of data, data format, or number of characters that can be entered in a field. If the data entered in the field violates the validation rule, Access automatically prompts for a correct value.

30. Setting Validation Rules

31. Validation Text When a validation rule is violated, you can display your own text in the warning message box.

32. Summary • Create field input masks using the Input Mask Wizard • Set Required, Allow Zero Length, and Field Size properties • Define validation rules and text for entering data in a field

33. Independent Practice Activity

34. Unit 12: Using Advanced Query Features

35. Overview • Understand the differences between outer, inner, and self-join queries • Design outer, inner, and self-join queries • Generate calculated fields in a query • Modify the format of a displayed value • Use the expression builder • Create queries to add, delete, and modify data in a table and to produce new tables

36. Joins Queries that include data from more than one table are called joins. A join tells MS Access how the data between tables is related. You can create: • Outer joins • Inner joins • Self-joins

37. Outer Joins An outer join is a query in which all of the records from one table are joined to only matching records from another table.

38. Inner Joins If one table does not have a matching record in the second table, then no records from either table will appear in the query results. To avoid this, you can use an intermediate table to extract data from the original two tables. This intermediate, or join table, acts as a bridge between two tables that don’t have any related fields. This is used when a many-to-many relationship exists between two tables and uses primary keys from both tables to extract the data from the intermediate join table.

39. Self-Joins A self-join is a query that displays matching records from the same table when there are matching values in two fields.

40. The Expression Builder

41. Action Queries Action queries perform certain actions in tables. There are four types: • Append-append data from one table to another • Delete-delete records from tables based on specific criterion • Update-update data in different tables at the same time • Make-Table-creates a table from the result of a query

42. Summary • Understand the differences between outer, inner, and self-join queries • Design outer, inner, and self-join queries • Generate calculated fields in a query • Modify the format of a displayed value • Use the expression builder • Create queries to add, delete, and modify data in a table and to produce new tables

43. Independent Practice Activity

44. Unit 13: Creating Advanced Queries

45. Overview • Use a select query to summarize and group data from different tables • Write a concatenation statement to combine values from different fields • Use the Find Unmatched Query Wizard to locate records that do not match between tables • Design a crosstab query to summarze grouped data • Create single- and multiple-criteria parameter queries • Learn how to use a wildcard in a parameter query • Create single- and multiple-field indexes to quickly sort and filter data in a table

46. Concatenation Concatenation is the process of combining values from different fields into one field

47. Find Unmatched Queries Use a Find Unmatched Query to view records that don’t have a matching record in another table Example: Locate customers who don’t have matching entries in the Orders table.

48. Crosstab Queries Display a summary of values based on two different types of information. Generally used to display large amounts of data in a compact format for easy review. Used to calculate averages, sums, or other totals on records, then group the results into two types of data. One group runs down the left side of the datasheet while the other runs across the top.

49. Parameter Queries A parameter query displays results based on criteria you specify each time you run the query. One or multiple parameters can be used.

50. Indexes Indexes are used to quickly sort and find data in tables. An index performs a search based on key values. Primary keys are automatically indexed.