Lesson 4: Pet sitters

In this lesson, you will learn about the importance of validation rules in ensuring your database contains high quality, usable data, and you’ll connect two tables using a relationship to make a relational database.

Learning objectives

Understand the term validation
Learn about different forms of validation
Understand the importance of validation in data gathering
Build a table from scratch, including validation rules

Types of validation check

Rule	Description
Presence check	A field with a presence check cannot be left blank. In Access, this is achieved by ticking Required in field properties.
Type check	A type check prevents input of data in incorrect types, e.g. not allowing letters to be typed into a number field.
Length check	A length check can prevent data that is longer than the maximum limit from being entered, e.g. allowing up to 15 characters for a First Name field.
Range check	A range check can be applied to any numerical data (numbers/currencies/dates) to prevent data outside the range from being entered, e.g. Number in stock in a shop database must be between 0 and 50.
Input mask	Dictates the format of what can be typed in. This is useful for telephone numbers by adding structure: (000) 000-0000.

Is it valid?

Input masks

Input masks are really helpful when data needs to have a specific format, like a telephone number: (0986) 555-1495 or a registration number: G59 6APW. Using the table below as a guide, type out the input masks for the questions that follow.

Character	Description
0	Enter a digit (0 to 9).
9	Enter an optional digit (0 to 9).
L	Enter a letter.
A	Enter a letter or a digit.
>	Convert all letters to uppercase.
<	Convert all letters to lowercase.

Using this information as a guide, let’s look at input masks for the examples above. The telephone number (0986) 555-1495 would have the input mask (0000) 000-0000. The postal code G59 6APW would have the input mask L00 0LLL.

Task: Pet Sitters

Download pet-sitters.accdb and save it into your Z:/ drive. Take a look at the two tables in the database and answer the questions below to get familiar with the data set.

Open tbl_Customer and go in to Design View.
We’re going to add some validation rules to this table to reduce errors.
1. Set the Field Size of First Name, Last Name and Pet Name to 15.
2. Add an input mask to the Phone field to only accept three numbers, a dash and four numbers, e.g. 555-1234.
3. We only accept pets aged between 2 and 15. Add a range check to the validation rule for the Pet Age field.
4. Leave design view and save the table.