USA: +1(669)289-8683 
Sign in
Data Entry with Access 2013
Individual Access Assignment – #1
Fall 2013 – Section 5
Jake’s Canine Pet Club
Due: October 27th, 2013
The individual student must do all work; any evidence of group work will result in failing grades
for all involved and the students will be referred to the student discipline officer for final
resolution without exception.
Any project turned in after the due date & time of October 27th, 2013 at 11:00PM will receive a
20% deduction from their final grade if turned in within 48 hours.
No assignments will be accepted after that date for any reason.
The project must be done in a single Microsoft Access 2013 file and turned in using iLearn.
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, Roger Healy found himself facing a dilemma. As a result of a chronic
illness; Jake, his beloved boxer, 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 Jake received daily exercise was another matter. Although
Jake’s daily exercise usually took the form of an occasional walk around the
neighborhood, Mr. Healy, as a busy college student, had trouble scheduling Jake’s
daily walk. Mr. Healy’s friend, Daniel, would walk Jake on the days when Mr. Healy
was extremely busy. Mr. Healy’s friends and neighbors liked the pet walking idea so
much that they approached Mr. Healy and Daniel about walking their dogs as well.
What began as a neighborhood walking service has now become a fledgling, yet
2
growing, local business. Mr. Healy has hired you to build a simple, yet effective,
database for his business.
He needs you to build Client and Pet forms, Client, Walker, Schedule and Pet tables,
Walker Schedule and Client List reports, and several queries.
CASE SCENARIO
Jake’s Canine Pet Club is a dog-walking service, catering to caring, yet busy, pet
owners. The service proved 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 new pets per week. Paperwork is increasing, and Roger Healy, the service’s
owner, needs a better record-keeping system.
During a meeting with Mr. Healy, he 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, Mr. Healy wants the available walk times, as well as walker, client,
and pet information, kept in the database that you are building.
Mr. Healy’s record-keeping needs are simple. He requires a database that tracks his
clients, their pets, available walk times, and the pet walkers. Mr. Healy 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 Jake’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.
Jake’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
3
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.)
Table 1: Client Table Structure
Field Name Data Type Field Description Field Size Comments
ClientNo AutoNumber
Is a unique, identifying number
assigned to each client. Serves
as primary key.
Long Integer
Is required.
CLastName Text Is the customer’s last name. 35 Is required.
CFirstName Text Is the customer’s first name. 20 Is required.
CAddress Text Is the customer’s street address. 30 Is required.
CCity Text Is the customer’s city. 25 Is required.
CState Text
Is the customer’s state
abbreviation. Make “CA” the
default value.
2 Is required.
CZip Text Is the customer’s zip code.
Use an input mask.
10 Is required.
CPhone Text
Is the customer’s home phone
number. Use for non-emergency
contact. Use an input mask.
11 Is required
CEhone Text
Is the customer’s cell phone
number. Use for emergency
contact. Use an input mask.
11 Is required.
4
Table 2: Pet Table Structure
Field Name Data Type Field Description Field Size Comments
PetNo Text
Is a unique identification number
that is assigned to each pet.
Serves as primary key.
10 Is required.
PetName Text Stores the pet’s name. 25 Is required.
ClientNo Number
Is the client identification
number. Must match a client
number from the Client table.
Use the Lookup Wizard.
Long Integer Is required.
WalkerNo Text
Is the walker identification number.
Must match a walker identification
number from the Walker table. Use the
Lookup Wizard.
4 Is required.
QuotedPrice Currency
Stores the daily walk fee. Mr.
Healy determines the actual price per
pet.
Is required.
WalkTimeCode Text
Is the code designating the preferred
time for walking the pet. Use the
Lookup Wizard.
2 Is required.
EnrollmentDate Date/Time Indicates when the client enrolled the
pet. Use the short date format.
Is required.
Comments Memo
Contains any additional information
that is necessary.
5
Table 3: Walker Table Structure
Field Name Data Type Field Description Field Size Comments
WalkerNo AutoNumber
Is a unique, identifying number
that is assigned to each walker.
Serves as primary key.
Long Integer Is required.
WLastName Text Is the walker’s last name. 50 Is required.
WFirstName Text Is the walker’s first name. 25 Is required.
WAddress Text Is the walker’s street address. 25 Is required.
WCity Text Is the walker’s city. 25 Is required.
WState Text Is the walker’s state abbreviation.The default is CA. 2 Is required.
WZip Text Is the walker’s zip code.
Use an input mask.
10 Is required.
WPhone Text
Is the walker’s home phone
number. Use for non-emergency
contact. Use an input mask.
8 Is required
WHireDate Date Is the walker’s date of hire Is required
Table 4: Walk Table
(You are responsible for designing this table on your own)
After studying your notes, you decide three relationships are necessary. First, a
relationship between the Pet and Client tables is needed. Since each table contains a
ClientNo field, you use the ClientNo field to create the relationship. Second, a
relationship between the Walker and Pet tables is necessary. The Walker and Pet
tables have a WalkerNo field, and you use this field to create a relationship between
the Walker and Pet tables. Third, both the Walk and Pet tables have a WalkTimeCode
field. You use the WalkTimeCode field to create the relationship between the Walk and
Pet tables. You decide each relationship should enforce referential integrity. (Note: The
Lookup Wizard will create relationships for you. However, you need to edit these
relationships to enforce referential integrity.)
6
Input Specifications
Figure 1 provides a tentative sketch for the Client form that Mr. Healy wants to use.
When a new client enrolls his pet with the walking service, Mr. Healy uses this form to
capture contact information about the client, such as his name, address, and phone
number. As the tentative sketch shows, the form header includes the service’s name
and the form’s name. After studying the tentative sketch, you use the Form Wizard to
build an initial Client form. Once the form is built, you edit the form in Design view.
FIGURE 1: Client Form
CLIENT
Client No: Street Address:
Client Last Name: City:
Client First Name: State: Zip:
Client Phone:
Emergency Phone:
After a client registers, Mr. Healy enrolls his pet(s). The pet enrollment process is simple and
captures basic information about the pet, such as the pet’s name, walk time, and walker
preference. You use the Form Wizard to build the initial Pet form. Once the initial form is built,
you edit the form in Design View. Figure 2 shows the initial Pet form sketch.
7
FIGURE 2: Pet Form
Pet
Client No: Walker No:
Pet No: Walker Time Code:
Pet Name:
Enrollment Date: Quoted Price:
Comments:
Information Specifications
Mr. Healy requests Walker Schedule and Client List reports. The Walker Schedule report s
generated on a weekly basis and tells Mr. Healy when his walkers are scheduled to walk the
pets. As the Walker Schedule report uses data from four tables, you build a select query, and
then base the report on the select query.
As the tentative sketch in Figure 3 shows, the Walker Schedule report header contains the
service’s name, a report title, and the current date. The information in the report body lists the
walkers in ascending order eased on the walker’s last name. A secondary sort is performed on
the walk time code, and within the walk time code category, the information is sorted based on
the client’s last name. Mr. Healy also mentions that she wants the Walker Schedule report to
utilize a landscape orientation.
Figure 4 shows a tentative sketch for the Client List report. The Client List report provides a
sting of the service’s current clients. You use the Report Wizard to speed initial report
development, and then edit this report in Design view. The Client List report’s header contains
the service’s name, report name, and current date. To maintain a consistent appearance with
the Walker Schedule report, you use a report style similar to the Walker schedule report.
8
FIGURE 3:
Walker Schedule
Walker Schedule
(Current Date)
Last
Name
Code Client Pet Name Address City Phone E-Phone
FIGURE 4: Client Report List
Client List
(Current Date)
Client Name Address City Phone
9
Mr. Healy needs answers to the following questions.
Build queries to help Mr. Healy 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 San Francisco? 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, hic,
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.
Test Your Design
After creating the forms, tables, relationships, queries, and reports, you should test
your database design. Perform the following steps.
1. In addition to the pet walker’s base pay, Mr. Healy wants to give each pet walker
a 10 percent commission for each pet that he walks. The commission is based on the
fee charged to walk the pet. What is the total commission for each pet walker?
Provide the walker’s first and last name and his total commission.
2. Mr. Healy wants to know the number of clients she has in each town. Provide
the name of the town and the number of clients for each town.
3. Mr. Healy is considering raising his fees. He would like to raise the fee for the
most popular time. Which walk time is most popular? Provide the walk time
description and a count of the number of pets walked at that time.
10
4. On average, how much does Mr. Healy charge his clients for walking their pets?
Show only the average.
5. Two new clients have enrolled with the pet walking service. Enter their information,
along with the information about their pets, into the database. For each client, assign
the next available client identification number. For each pet, assign the next available
pet identification number and add any comments that you feel are necessary.
a. Muffy Perlin lives at 48473 Roosevelt Drive in Berkeley, California. The zip
code is 93002; her phone number is 943-8789, and her emergency
number is 910-5746. Mickey, Precious, Prancer, and Spot are her four pets.
Mickey is a Yorkshire terrier, Precious is a poodle, Prancer is a Daschund, and
Spot is a Dalmatian. The three small dogs cost $6.50 to walk, and the larger dog
costs $8.50 to walk. Ms. Tibbs wants Bob Legier to walk each dog in the
early morning. Use today’s date as the enrollment date.
b. Norman Dumont lives at 84739 Park Lane in Alameda, California. The zip
code is 93250; his phone number is 748-0098, and his emergency
number is 748-9876. Mr. Dumont has two pets. Lightning is a poodle and
costs $6.50 to walk. Sunshine is a Great Dane and costs $10.50 to walk.
Mr. Dumont requests that Kelly Lamont walk his dogs in the late
afternoon. Use today’s date as the enrollment date.
CASE DELIVERABLES
In order to satisfactorily complete this case, you should build the database and turn in
an electronic, working copy of your database that meets the criteria mentioned in the
case scenario and specifications sections.
GRADING CRITERIA
Following Directions / Completeness – 25%
Tables – 15%
Queries – 15%
Reports – 15%
Forms – 15%
Relationships between the Tables – 15%
>Clients
Jakes’s Canine Pet Club Client Table Data C A ddress 000 B lake 9 003 1 ) 606-8975
000 2 CA CA Daly City CA 93099 CA CA Pacifica CA CA Daly City CA (415) 425-5559 Daly City CA 93003 (415) 381-1777 San Francisco CA 93001 (415) 454-0650 San Francisco CA 93001 (415) 273-6169 San Francisco CA 93001 (415) 273-9314 Burlingame CA 93003 (415) 899-5840 Brisbane CA 93250 (415) 912-4181 Brisbane CA 93250 (415) 378-8080 Pacifica CA 93002 (415) 943-2481 CA 93069 (415) 658-2138 Brisbane CA 93002 (415) 943-1198 Daly City CA 93003 (415) 425-0914 Jakes’s Canine Pet Club Pet Table Data
2
CNo
CLastName
CFirstName
CCity
CState
CZip
CPhone
CEmergencyPhone
1
Barney
101 Sunnyville Lane
Daly City
CA
3
(
4
5
(415) 606-5039
Stone
David
1408 Peter Pan Drive
Pacifica
93069
(415) 658-8182
(415) 606-3402
0003
Yu
William
7120 Lakeridge
Burlingame
93099
(415) 899-4979
(415) 899-2222
0004
Monac
Levitica
303 Northridge
(415) 381-2395
(415) 909-8679
0005
Ruaz
Monica
1701 Memorial Road
San Francisco
93001
(415) 905-8440
(415) 606-4102
0006
Barker
Gayle
1983 Sliding Glass
Millbrae
93034
(415) 754-3666
(415) 359-1588
0007
Burke
Tim
57891 Roosevelt Drive
93002
(415) 943-8231
(415) 910-5625
0008
Dillon
Lester
87896 Park Lane
Brisbane
93250
(415) 748-0101
(415) 748-9965
0009
Benton
Michael
1713 Bentonville Road
93003
(415) 425-5559
0010
Dubell
Suzanne
1805 Southwest Pickard
(415) 381-1777
0011
Blasko
Jason
459 Harrison Drive
(415) 454-0650
0012
Fentem
Dean
1947 Oak Avenue
(415) 273-6169
0013
McCoy
Drew
615 Morgan Drive
(415) 273-9314
0014
Sephin
Gary
4201 Morgan Drive
(415) 899-5840
0015
Treece
Evelina
1904 South Parkville
(415) 912-4181
0016
Levendusky
Bertha
12 Broadway
(415) 378-8080
0017
Ruhstaller
Parker
204 West Dakota
(415) 943-2481
0018
Chu
Pui
211 Amber Avenue
San Mateo
(415) 658-2138
0019
Arie
Katherene
1004 Eagle Drive
(415) 943-1198
0020
Kuwitzky
Shinya
1204 New Bedford
(415) 425-0914
Pets
PNo
CNo
1
0002
3
0003 W003 $8.00 3
0004
3 8/22/12
0004 W004 $8.00 3 8/22/12
0004 W004 $8.00 3 8/22/12 Is a Black Labrador.
0005
$10.00 2
0005 W002 $7.00 2 7/12/12
0006 W004
5
can be aggressive towards other animals.
0006 W004 $8.50 5 8/13/12
0006 W004 $10.00 5 8/13/12
0007 W001
1
0007 W001 $6.50 1 10/28/12
0007 W001 $6.50 1 10/28/12
0007 W001 $6.50 1 10/28/12
0008 W004
4
0008 W004 $8.50 4 11/1/12
0009
$6.50 1
0009 W005
1 8/1/12
0009 W005
1 8/1/12
$7.00 1 6/1/12
0010 W010 $6.00 1
0010 W010 $6.00 1 8/2/12
0011
$7.50 5
Keep on a leash.
0011
$10.00 5 8/3/12
0012 W009
3
0013
$6.00 2 8/4/12
0013 W007 $6.00 2 8/4/12
0014
1
0015 W005 $7.00 4 8/5/12
0015 W005
4 8/5/12
0016 W010 $6.50 2
0016 W010 $6.00 2 8/6/12
0017 W008 $7.00 3
0018 W009 $8.00 1
0019 W007 $7.00 4 8/8/12
0020 W006 $7.00 3 8/8/12
1
2
3
A
B
PNo
PetName
0002
Fiedo
Pet Table Data
