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

RuleDescription
Presence checkA field with a presence check cannot be left blank. In Access, this is achieved by ticking Required in field properties.
Type checkA type check prevents input of data in incorrect types, e.g. not allowing letters to be typed into a number field.
Length checkA 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 checkA 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 maskDictates 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.

CharacterDescription
0Enter a digit (0 to 9).
9Enter an optional digit (0 to 9).
LEnter a letter.
AEnter 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.

  1. Open tbl_Customer and go in to Design View.
  2. We’re going to add some validation rules to this table to reduce errors.
    1. Set the Field Size of First NameLast 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.
Find out how

  1. Open tbl_Sitter and go in to Design View.
    1. Reduce the field size of Sitter ID to 2 and apply an input mask that requires 2 letters.
    2. Add a range check to the Zip field that only accepts values between 12800 and 12999.
    3. Leave design view and save the table.
  2. We have just recruited two new sitters to fill our vacancies. Add their details to the database:
  1. In tbl_Customer, Gerry Trent’s cat Missy can now be assigned a sitter. Check who is available in her town to look after cats and type the correct sitter ID in.

Making a relationship

  1. We now have two tables of data that can be linked by building a relationship. Create a one-to-many relationship between tbl_Sitters and tbl_Customers using the Sitter ID field.
Find out how

Input form

  1. If we’re going to add data to a database, it’s helpful to make an input form. This shows only one record at a time and makes it much easier for a user to add data without making mistakes. Include this logo.
  2. Create a form (called frm_Customer) based on tbl_Customer.
  1. Add the following records to your database using the input form you just made.

 123
Customer IDP04P05W04
First NameLaurenaDanPhylis
Last NameCadiganCharlesGee
Street258 Prairie St.9 Summer Rd.49 Crescent Ave.
CityPleasantviewPleasantviewWillow Creek
Phone555-8845555-4721555-7031
Pet TypeDogDogCat
Pet NameMiloSashaTucker
Pet Age11813
  1. Download the waiting list file (sitterswaitinglist.csv) and append them to the bottom of your tbl_Customer table.
  1. Using the table below, use filters in the tbl_Customer table to assign the correct sitter for each pet who doesn’t yet have one.

Sitter IDCityDogsCats
AKPleasantview
CRThree Lakes
JLWillow Creek
RGPleasantview
SLWillow Creek

Consolidation & practice