KoKo's Canine Pet Club

SKILLS CHECK

You should review the following areas:

Aggregate Function

Relationship

Calculated Field

Report Design

Form Design

Report Wizard

Form Wizard Select Query

Lookup Wizard

Table Design

CASE BACKGROUND

Six months ago, Caedee Hannah found herself facing a dilemma. As a result of a chronic

illness, KoKo, her beloved schnauzer, required medication, a special diet, and daily

exercise. While the medication and special diet were easy to accommodate into a busy life

style, ensuring that KoKo received daily exercise was another matter. Although KoKo's daily

exercise usually took the form of a walk around the neighborhood, Ms. Hannah, as a busy

executive, had trouble scheduling KoKo's daily walks. Ms. Hannah's friend, lan, would walk

KoKo on the days when Ms. Hannah was extremely busy. Ms. Hannah's friends and

neighbors liked the pet walking idea so much that they approached Ms. Hannah and lan

about walking their dogs as well. What began as a neighborhood walking service, has now

become a fledgling, yet growing, metropolitan area business. Ms. Hannah has hired you to

build a simple, yet effective, database for her business. She needs you to build Client and

Pet forms, Client and Pet tables, Walker Schedule and Client List reports, and several

queries.

CASE SCENARIO

KoKo's Canine Pet Club is a dog-walking service, catering to caring, yet busy, pet owners.

The service proves very popular with pet lovers who recognize the value of providing their

pets with daily exercise. Although the service was only started six months ago, it currently

provides pet walking services for 50 pets and is registering, on average, 5 pets per week.

Paperwork is increasing, and Caedee Hannah, the service's owner, needs a better

record-keeping system.

During a meeting with Ms. Hannah, she explains to you that a new client must register with

the service. During the registration process, the new client provides basic information about

his pet(s), chooses a preferred walk time for his pet(s) and specifies a walker preference.

During this time, a dog-walking fee is determined and recorded on the pet registration form.

The dog-walking fee varies by pet and is based on the pet's size, temperament, and the

number of pets the owner has. The pet owner can request that his pet be walked in the early

morning, late morning, early afternoon, late afternoon, or early evening hours. Available

walk times are currently kept on a clipboard by the phone. However, Ms. Hannah wants the

available walk times, as well as walker, client, and pet information, kept in the database that

13

Database Case

you are building.

Ms. Hannah's record-keeping needs are simple. She requires a database that tracks her

clients, their pets, available walk times, and the pet walkers. Ms. Hannah gives you a

partially completed database and requests that you build and populate Client and Pet tables,

create several relationships, design Client and Pet forms, design Walker Schedule and

Client List reports, and construct several queries.

Storage Specifications

After reviewing the partially completed KoKo's Canine Pet Club database, you notice that

the database currently contains Walker and Walk tables. The Walker table stores basic

information about each walker, and the WalkerNo field serves as the table's primary key.

The Walk table stores a set of walk time codes. When a client registers a pet, a walk time

code is assigned to each pet. This walk time code indicates the pet owner's preference for

the time of day when the pet should be walked.

KoKo's Canine Pet Club database requires both Client and Pet tables. You decide the Client

table should store contact information for each client and that the client identification number

should serve as the primary key. Table 1 shows the structure for the Client table. (Your

instructor will provide you with the data to populate the Client table.)

The Pet table stores information about each pet, including the pet number, pet name, client

identification number, walker identification number, quoted price, preferred walk time,

enrollment date, and any relevant comments. Table 2 shows the structure for the Pet table.

(Your instructor will provide you with the data to populate the Pet table.) As you study this

structure, you notice that the WalkerNo and WalkTimeCode fields are part of the Pet table

structure. As the WalkerNo and WalkTimeCode fields already exist in other tables, you use

the Lookup Wizard to create these fields in the Pet table. By using the Lookup Wizard, you

can facilitate data entry and ensure accuracy for both fields. (The Lookup Wizard is invoked

when you select the Lookup Wizard as the data type for the field.) ...........

Ms. Hannah needs answers to the following questions. Build queries to help Ms. Hannah

answer these questions. If you choose, you may generate reports based on these queries.

1. How many pets does each pet walker currently walk? Show the walker's first and last

name and the pet count for each pet walker. Sort the information in ascending order based

on the pet walker's last name.

2. Which clients are located in Edmond? Provide their last and first names.

3. Which clients have three or more pets? Show each client's first and last name and the

number of pets he currently has.

4. What are the total pet walking fees charged to each client? Show the client's first and last

name and the total fees charged to him.

5. Which pets does Bob walk in the early morning? For each pet, show the pet's name, his

owner's last name, and his owner's phone number.

Implementation Concerns

Although you are free to work with the design of the forms and reports, each form and report

should have a consistent, professional appearance. Consider using the wizards to prepare

the initial forms and reports. Once you have prepared the initial forms and reports, you can

edit them in Design view.

A lookup field enables the end user to select a value from a list, thus facilitating data entry

and promoting data accuracy. You should define the ClientNo, WalkerNo, and

WalkTimeCode fields in the Pet table as lookup fields. When defining the data type for each

field, select the Lookup Wizard in the Data Type column and follow the directions in the

Lookup Wizard dialogue boxes.