Individual Access Assignment - #1

Individual Access Assignment - #1 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 April 5, 2015 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 growing, local 2 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% "Jakes's Canine Pet Club Pet Table Data" PNo    PetName    CNo    WalkerNo    QuotedPrice    WalkTime    EnrollmentDate    Comments 0002    Fiedo    0001    W001    $10.00    1    6/1/2014    Is a friendly miniature schnauzer.  Keep on chain.  He will run away, if he is off his chain. 0005    Bruno    0002    W003    $8.00    3    8/15/2014    Is a well-behaved Chow Chow. The owner wants Bruno walked by himself and not with other pets. 0006    Sally    0003    W003    $8.00    3    8/22/2014    Is a well-behaved mixed breed.  She does frighten easily.  Please keep her on her leash. 0007    Peek-A-Poo    0004    W004    $7.00    3    8/22/2014    Is a mixed-breed.  Very playful. 0008    Bear    0004    W004    $8.00    3    8/22/2014    Is a Black Labrador. 0009    Gretchen    0004    W004    $8.00    3    8/22/2014    Is a Black Labrador. 0003    Molly    0005    W002    $10.00    2    7/12/2014    Is a sensative Pomeranian.  Needs lots of attention. 0004    Sir Lancelot    0005    W002    $7.00    2    7/12/2014    Is a playful German shepard, but is hestitant around strangers. 0010    Prancy    0006    W004    $8.50    5    8/13/2014    Mickey can be aggressive towards other animals. 0011    Lacy    0006    W004    $8.50    5    8/13/2014    Is very affectionate.  Lacy will run, if she gets off of her leash. 0012    Dazzle    0006    W004    $10.00    5    8/13/2014    Dazzle is afraid of strangers. 0013    Lion King    0007    W001    $6.50    1    9/28/2014    Lion is a pit bull. 0014    Mosie    0007    W001    $6.50    1    9/28/2014    Mosie is a poodle. 0015    Finley    0007    W001    $6.50    1    9/28/2014    Finley is a Daschund. 0016    Dosie    0007    W001    $6.50    1    9/28/2014    Dosie is a Dalmatian. 0017    Kipper    0008    W004    $4.50    4    11/1/2014    Kipper is a small dog 0018    Pouncer    0008    W004    $8.50    4    11/1/2014    Pouncer is a Great Dane. 0019    Beau    0009    W005    $6.50    1    8/1/2014    Beau is a nice, loving animal.  Tries to be the boss sometimes. 0020    Lulu    0009    W005    $6.00    1    8/1/2014    Keep on a leash. 0021    Sunshine    0009    W005    $7.50    1    8/1/2014    Is afraid of cars. 0001    Mickey    0010    W010    $7.00    1    6/1/2014    Is a very friendly, well-behaved poodle. 0022    Penny Pen    0010    W010    $6.00    1    8/2/2014    Does not like cats. 0023    Winston    0010    W010    $6.00    1    8/2/2014    Loves children. 0024    Lavender    0011    W009    $7.50    5    8/3/2014    Keep on a leash. 0025    Smokey    0011    W008    $10.00    5    8/3/2014    Is friendly. 0026    Shelby    0012    W009    $5.50    3    8/4/2014    Loves attention. 0027    Rocky    0013    W007    $6.00    2    8/4/2014    Enjoys his walks tremendously. 0028    Max    0013    W007    $6.00    2    8/4/2014    Loves to chase butterflies. 0029    Felix    0014    W006    $11.00    1    8/5/2014    Can be aggressive around other dogs. 0030    Jasper    0015    W005    $7.00    4    8/5/2014    Please walk only in the neighborhood.  Do not take out by the street. 0031    Hudson    0015    W005    $5.00    4    8/5/2014    Likes to run and play. 0032    Ace    0016    W010    $6.50    2    8/6/2014    Please walk only in the park. 0033    Ginger    0016    W010    $6.00    2    8/6/2014    Barks a lot, but is harmless. 0034    Duchess    0017    W008    $7.00    3    8/7/2014    Likes to chase the birds. 0035    Sophie    0018    W009    $8.00    1    8/8/2014    Please give a treat immediately after walking. 0036    Rex    0019    W007    $7.00    4    8/8/2014    Needs to trust the pet walker before he is comfortable. 0038    Oslo    0020    W010    $7.50    3    8/13/2014    Very Food motivated and friendly 0039    Penny Pen    0020    W010    $7.50    3    8/13/2014    Hyper dog that loves to play ball