Day 20: Access Chapters 5, 6, 7

1. Day 20:Access Chapters 5, 6, 7 Larry Reaveslarry.reaves@mail.wvu.edu October 28, 2013

2. Last class • Reports • Design View • Layout View • Report View • Print Preview • Report Wizard • Labels

3. Data Validation • Data validation is a set of properties that controls how data is entered into a field • Required • Default Value • Validation Rule • Input Mask

4. Required • A required field must have a value when you create a new record. It can not be left blank. • By default, required is set to no for all fields except for the primary key. • Primary keys are required since they are used to identify the records.

5. Default Value • If you have a common value in a field that is used more often than not you can set it as the default value for the field. • The default value will automatically be used when you create a new record and leave the field blank. • The Date() function can also be used as a default value to use the current date for new records

6. Validation Rule • Validation rules restrict the data values that can be entered into a field • Examples: >= 7.5 > #01/01/2010# • Validation rules do not make the field required

7. Validation Text • Validation text allows you to specify a message that will be displayed when the validation rule is broken • If validation text is not entered the user will see the default message: “One or more values are prohibited by the validation rule ‘rule’ set for ‘TableName.FieldName’. Enter a value that the expression for this field can accept.”

8. Input Mask • Input masks allow you to restrict the data being input for text and data types to exactly the format you specify • Examples: • Phone numbers • Social Security numbers • Zip codes • Dates

9. SQL • All queries are handled internally as SQL • SQL stands for Structured Query Language • You can see and edit the SQL that makes up a query from SQL View

10. SELECT • A SELECT statement is used to retrieve data from the tables in a database • Four main keywords • SELECT • FROM • WHERE • ORDER BY

11. Join Types • INNER JOIN • Only records where the keys match • RIGHT JOIN • All records from the right table and matches from the left • LEFT JOIN • All records from the left table and matches from the right

12. Next Class • Access Exam Review Project