Lesson 5: Contacts database

In this lesson, you will put the skills you have learned in the last four lessons to use in building a contacts database from scratch.

If at any time you feel like you need a refresher on vocab, head back to the unit overview page for some quizzes and revision tools.

Learning objectives

  • Build a database from scratch
  • Set up field names & data types
  • Create validation rules to limit errors
  • Add data to this database

In this first task you will learn some of the fundamental skills you need to get started with databases, including:

  • Creating a flat file database
  • Creating a table
  • Setting a primary key
  • Setting data types
  • Setting validation rules
    • Presence check
    • Length check
    • Range check

Follow the instructions step-by-step, and expand the “Find out how” sections for more guidance if you need it. Please note that sometimes excessive server load can cause the website to load slowly. The video tutorials on this page are also available on Youtube.

Important: To start this task, you need to be familiar with the four basic items of database vocab: database, table, field & record. If you don’t know what these terms mean, take a look at this short quiz before you get started.

Instructions

1. Open Microsoft Access and create a new database called Contacts.accdb

2. Create a new table called tbl_Contacts and make the following fields in design view:

Field nameLengthData type
First Name20Text
Initial1Text
Last Name20Text
Street Address50Text
City20Text
State2Text
Zip CodeDoubleNumber
Tel12Text
DoBN/ADate/Time

* Note: We haven’t added a primary key (ID field) yet. Don’t worry – we’ll do it later!

Find out how

3. Add the following validation rules to the fields in this table:

Field nameRequired?Validation Rule
First NameYes
InitialNo
Last NameYes
Street AddressYes
CityYes
StateYesLookup, use usstatecodes.csv
Zip CodeYesMust be four or five numbers
TelYesInput mask. Format: ###-###-####
DoBNoMust be between 01/01/1940 and 31/12/1985

Find out how

4. Add the following records to your table:

First NameInitialLast NameStreet AddressCityStateZip CodeTelDoB
BernadetteWeissman4767 Clair StreetWacoTX76710254-730-909018/09/1955
EdgarBRhodes3633 Hill Croft Farm RoadChicoCA95926530-540-661917/04/1970
MichaelTBarber65 Hickory Heights DriveBel AirMD21078443-843-146411/03/1943

5. Import the records from contacts.csv and append them to the bottom of your table.

6. In design view, insert a new field at the top of the list. Call it ID, make it the primary key, and set the data type to autonumber.

Congrats! You now have a contacts database with 5,000 records in it! See how easy it is to make a huge database when you keep control of your data types and validate data entry?

Key takeaways

What important ideas are you going to take away from this activity?

Example: Error messages when importing data tell you that you have problems with your validation rules.

Master these skills: consolidation task

1. Create a database called Customers.accdb and make a new table called tbl_Customers inside it.

2. Add the following fields (in this order) to your table:

  • First name
  • Middle name
  • Surname
  • Email address
  • Gender
  • Social Security number
  • Street
  • City
  • State
  • Telephone
  • Credit card issuer
  • Credit card number
  • Credit card expiry

3. Download this dataset (customerdata.csv). Take a good look at it in Excel before importing it to Access so you understand what validation you can apply.

  • Choose appropriate data types for each field.
  • Apply validation rules where appropriate to each field. You can make a copy of the template to the right to note down the validation rules you have chosen.

  • You could use any of the following:
    • Presence check
    • Length check
    • Type check
    • Range check
    • Format check (input mask)
    • Lookup

Optional: You may want to record your own short video tutorials of how you did each of the steps for you to refer to later. Use Screencast-O-Matic to do this.