USA: +1(669)289-8683 
Sign in
Straight forward, I need this assignment completed just as the directions say to do the work. The direction for each step or part of the assignment are in attach word software listed as Access Assignment Directions.
Access Assignment Directions
|
Access Tutorial 1: Mini-Case 1
Scenario: Your Task: Access Table 1-4 Fields for Mini-case 1 database Field Description ID Number A 6-digit identification chosen by Nick that may use both numbers and letters. Dealer Cost The price that Nick paid for the car. Should be stored in the appropriate currency format. Blue Book The estimated used car sales price based on the industry standard “Blue Book” estimates. Should be stored in the appropriate currency format. Type The type of vehicle. Data values here can include car, truck, SUV, etc. Sold A Yes/No field that can be checked as Yes for vehicles that have been sold. Create a form for entering data into the vehicles table. Use your form to enter the data listed in Access Table Access Table 1-5 Data for Mini-case 1 database ID Cost Blue Book Type Sold 01086 $13,550 $17,888 Car Yes 01145 $10,500 $12,599 Truck No 01319 $8,100 $9,300 SUV No A0554 $11,430 $12,800 Car Yes |
|
Access Tutorial 1: Mini-Case 2
Scenario: Your Task: Access Table 1-6 Fields for Mini-Case 2 database Field Description photo_ID A photo ID number that can be automatically generated. photographer The name of the family member who took the photo. date The date when the photo was taken. location The location where the photo was taken. subject A brief description of the photo’s subject matter. color A Yes/No field that can be checked as Yes for color photos and No for black/white photos. filename The file name of the photo stored on Amy’s hard drive. Create a form for entering data into the vehicles table. Use your form to enter data. Enter about 10 records. An example record is provided in Access Table Access Table 1-7 Example data for Mini-Case 2 database Field Data photo_ID 1 photographer Julie date 8/7/2007 location Burgundy, France subject The Roche de Solutre. A natural and prehistoric site in France. color Yes filename roche001 |
|
Access Tutorial 2: Mini-Case 1
Scenario: File: Your Task: · What are the names, departments, and salaries of all employees? (Save this query as EmployeeList.) · What are the names of all of the employees in the Accounting department? (Save this query as AccountingList.) · What are the names and departments of employees who have an annual salary of at least $100,000? (Save this query as 100000.) · What are the names, departments, and birthdates of employees with an annual salary between $30,000 and $50,000? (Save this query as 30kto50k.) · Who are the employees who have worked at the company since the year 2000? (Save this as after2000.) · Who are the employees who have worked at the company since the year 2000 and have an annual salary at least $35,000? (Save this as after 2000and35k.) · List the employees that are in both the Finance (FIN) and Operations (OPS) departments. (Save your query as FINorOPS.) · Which employees will have been with the company for more than 10 years at the start of 2007? (Save your query as 10Years.) · Which employees will have been with the company for more than 10 years or will be over 30 years of age as of the start of 2007? (Save your query as 10Yearsor30.) Make a report based on salary30kto50k. Group the data by the birth date and sort the data within groups by salary. |
|
Access Tutorial 2: Mini-Case 2
Scenario: Access Table 2-3 Tasks table fields Field Name Data Type Description taskDesc Text A description of the task that needs to be done. taskPriority Number (Integer) How important the task is relative to others in the database. Stored as a number from 1 to 5 with 1 being the most important. taskDueDate Date/Time The date when the task needs to be completed. For some items there may be no due date. taskCompleted Yes/No Whether or not the task has been completed. taskCategory Text The number of shares in the block of stock. File: Your Task: · What items are due within the next week? (Save this query as weeklyTasks.) · What tasks are stored for the “school” category? (Save this query as schoolTasks.) · Are there any tasks with a priority of 1 that are due today? (Save this query as highToday.) · How many tasks for the “Work” category have been completed? (Save this query as workDone.) · How many tasks from last week were completed? (Save this as lastCompleted.) · Which tasks are stored that have no due date or a priority of 5? (Save this as lowTasks.) Also, create a report based on one of the queries that will show the tasks for the next week in order of due date and priority. You should make up data and enter it into your table to test the components that you create. |
|
Access Tutorial 3: Mini-Case 1
Scenario: Your Task:
Access Figure 3-46 Book inventory database. Create data entry forms for your database. Make one form for each table. · What authors (first and last name) wrote what books (title, year published, and description) by what publisher (publisher name)? (Save your query as bookList.) · Choose one of the authors in your database. What publishers published books by this author)? (Save your query as authorPub.) · What books if any have more than one edition (title, author, publisher, edition, year published)? (Save your query as edition.) · What authors wrote a book that was published before 1990? (Save your query as 1990Entries.) Create a report based on the query bookList. Group by authors’ last names. Subgroup by publisher name. |
|
Access Tutorial 3: Mini-Case 2
Scenario:
Access Figure 3-47 Car maintenance database. Your Task: · Which customers use which cars and at what daily rate? · What is the total daily rate for all cars? · Which mechanics worked on a car for more than 5 hours? · Which cars have been worked on in the last 3 months? · Which customers still owe for repair work? (Create a query and a report based on the query.) |
|
Access Tutorial 4: Mini-Case 1
Scenario: File: Your Task: · Who are the students listed in the database? (Show all fields in student table; save this query as studentList.) · What is the major of Alexis Allen? (Save this query as alexis.) · Which students are over 30? (Show name and age; save this query as over30.) · What are the names, ages, and majors who for those students who enrolled in March 2007? (Save this query as march2007.) · Which students are journalism majors who enrolled in March 2000? (Save this query as marchJour.) · How many students were born on a Monday? (Save your query as monday.) · What is the average age of students who are majoring in computer science? (Save your query as avgCSCI.) |
|
Access Tutorial 4: Mini-Case 2
Scenario: Starter File: Tutorial 4: Mini-Case Link-Saver File: Your Task: · List the usernames and passwords. (Show all fields in user table; save this query as userList.) · What are the e-mails of all users who have entered a link? (Save this query as activeUsers.) · What are the URLs for all links that are in the Search category? (Do not show duplicates. Save this query as searchURLs.) · What links are available for .com sites? (Include title, link, and category. Save this query as dotComs.) · Write the SQL command to add a new link to the links table. (Save this query as addLink.) · Write the SQL command to add a new user to the user table. (Save your query as newUser.) · Write the SQL command to delete all records in the userLinks table related to user 1. (Save your query as linkDelete.) |
Microsoft Access Project 1: The WildOutfitters “How-To” Courses Database
Learning Objectives
By reading the business case sections and completing the three parts of this project (Parts A, B, and C) you will:
·
Extend your knowledge of Microsoft (MS) Access tables, forms, queries, and reports.
·
Understand the relationship between the logical model of a database and the physical implementation of that database.
·
Understand how to implement referential integrity and discover its purpose.
·
Learn how to apply Access to solve business problems.
Files for Download: Files associated with this project for you to download are the following:
·
The Mini-Case data model file, ACC_Project_WildOutfitters_Data_Model_1.pptx
Starter File: Project 1: Wild Outfitters
Project Introduction
Data and databases are at the heart of many business information systems. If you think about the daily interactions you have with organizations, you will discover that many of these interactions generate data that organizations use to create value for you and for them. For example, when you shop at a retail grocery store, data is gathered about your purchases and your method of payment. If you choose to pay with a debit or credit card, then additional data may be gathered about you and your purchase. Many stores offer a discount if you use some type of “customer” card. This enables the store to gather data about your specific purchases and to provide value to you through coupons and other sales promotions. It also enables store management to make better decisions about product selection, pricing, promotions, and so forth. This project will focus on how a retail firm might achieve its goals of generating new business and increasing customer satisfaction by providing a knowledge-oriented service to its customers. You will create the database that will allow this company to accomplish these goals.
General Business Case
Wild Outfitters, Inc. is a well established national outdoor-sports retail products chain with a local store in your area. It carries a complete line of name brand and locally produced equipment and supplies for outdoor enthusiasts. Through customer surveys, Wild Outfitters discovered that many of its customers are beginners and buy equipment but do not know how to use it properly. Rather than view this as a problem, Wild Outfitters sees this as an opportunity to provide better service to its customers and to gain a business advantage over its competitors. Wild Outfitters is going to offer “how to” courses for its customers. During these courses, customers will learn how to use the equipment they purchased, but they will also learn about other equipment they need and might want to purchase. Wild Outfitters will use this free (to the customers) service to increase sales and customer satisfaction. Isaac Timberlake, a student at your business school, is an intern at Wild Outfitters, and Sara Johns, the store manager, asks Isaac to create a Course Management System that she can use to track the courses customers take and the employees who teach the courses. Isaac understands the business requirements, but he needs your help in designing the database that will be at the heart of the Course Management System. The end result of your collaboration with Isaac will be the relational database and associated features specified in the MS Access Project instructions that follow.
Project Data Model Instructions
You will need to download the PowerPoint file ACC_Project_WildOutfitters_Data_Model.ppt. Your instructor will tell you how to do this. This file contains the data model you will reference in order to create your database. There are several slides in the presentation. The first slide is the data model, and the remaining slides are examples of how your tables might look. Remember, you will have to use MS Access to create the actual tables, and you will have to add additional data to meet project requirements.
Part A: Creating the Tables
Part A Problem Definition
Isaac Timberlake welcomed the challenge of creating an information system that will help create business value for Wild Outfitters, Inc. This project focuses on the database component of that IS, and accomplishing this project will enable you to help Isaac by applying and refining the IS skills you’ve learned. Before you can build the database, you will have to understand the data that must be captured by the system and sketch out how this data will be stored. You should try this on your own and then compare your model to the one found in the ACC_Project_WildOutfitters_Data_Model.pptx file.
Part A Problem Requirements
Remember, tables are the objects that MS Access uses to store data. Your first task in this part of the project is to help Isaac by using Microsoft Access to create the required tables (see the ACC_Project_WildOutfitters_Data_Model.pptx file). You should use input masks for fields where appropriate (e.g., zip code). If you do not know what an input mask is, use MS Access’s Help function to find out. Next, you will need to populate the tables with data. You should make up the data yourself, following the examples in the ACC_Project_WildOutfitters_Data_Model.pptx file. You should create at least 10 records (rows) in the customer table and five records each in the employee, class, and course tables. Your instructor may require that you add more data. You may want to accomplish Part B of the project before you enter all of your data. Lastly, you will need to create the appropriate relationships between the tables. When creating relationships, you should ensure that referential integrity is enforced.
Part A Interpretive Questions
Based on your work in Part A, answer the following questions:
1.
What is the relationship between an instance of a primary key and a record (row) in a table?
2.
What is referential integrity and why is it important for Isaac (and you) to enforce referential integrity in the database you create?
Part B: Inputting Data into the Database and Creating Forms
Part B Problem Definition
Isaac discovered that, rather than entering data directly into the tables, he could use the MS Access database objects known as forms to enter data into the database. He used the forms that you created to make data entry more efficient and less error-prone.
Part B Problem Requirements
Use the Form Wizard to create a form that will allow you to input data into each of the tables. Although the forms that you create are useful tools for entering data, when a customer makes a purchase, it would be useful to have the option to sign the customer up for a class.
Part B Interpretive Questions
Based on your work in Part B, answer the following questions:
1.
In Part A of the project, you may have entered data directly into the tables. Although this is convenient, why are forms a better way to do this? Why might it be a good idea to avoid entering data directly into the tables?
2.
Forms work well, but given additional hardware and software resources, how could you improve the data entry process to reduce errors?
Part C: Creating Different Views of Data for Decisions; Creating Database Queries
Part C Problem Definition
Ms. Johns asked Isaac to find out which employees are leading which courses and classes and when they are leading them. She also wants to know which customers are taking classes and when. Lastly, she wants to be able to view the data for each instructor and for each month. Although the data stored in your database is organized into tables, looking at all of the tables to answer her questions or to help her make decisions is inefficient. Fortunately, MS Access provides an object—the query—and a query tool—query by example (QBE)—which makes it easy for you to view selected subsets of your data. In other words, Access makes it easy for you to ask and answer questions about your data and then use this information to make business decisions.
For the purposes of this project, you can think of a query as a question you ask of your database. In addition to QBE, the visually oriented tool you will use here, Access also provides another tool to create queries, although this tool is actually a computer language. Structured Query Language (SQL) is a standardized language for querying databases. With it you can create complex queries of almost any database; however, using SQL is beyond the scope of your project requirements (see Part C Interpretive Question
2
).
Part C Problem Requirements
Using the Design view and QBE, create the following queries. You may need to add more data to your database so that you can test your query results.
|
Query 1: What customers have signed up to take classes from which instructors? (This query should return the data in your database for all courses, classes, customers taking classes, and employees leading classes.) Query 2: Who is taking and leading a course in September? (This query should return the courses, classes, customers taking classes, and employees leading classes for September only.) Query 3: What classes is Sara Johns teaching in September? (This query should return the courses and classes that Sara Johns is leading in September only.) Query 4: What classes are being lead by either Sara Johns or Isaac Timberlake in September? (This query should return the all of the courses and classes that Sara Johns and Isaac Timberlake are leading in September only.) |
|
Part C Interpretive Questions Based on your work in Part C, answer the following questions: 1. When querying a database using QBE, what are criteria used for? 2. When using QBE, what is the difference between asking, “What are all the classes that Employee A and Employee B are teaching?” and “What are all the classes that both Employee A and Employee B are teaching together?” 3. Although we mentioned that SQL was beyond the scope of this project, you can look at the SQL view for any of your queries. It is one of the choices on the same View menu that you used to select the design view after running your queries. Look at the SQL for Query 2 above. Can you make sense of the SQL query? Look for things like table and field names. You can even change the September query to an August query by changing only one character—try it! |
Part D: Outputting Data to Decision Makers and Other Knowledge Workers; Creating Reports
Part D Problem Definition
Queries are useful tools for asking questions of your data and for transforming data into information that is useful to decision makers. However, the standard query table view is not as useful or as easily readable as most users need or want. Ms. Johns asked Isaac to organize the views of Wild Outfitters data and present it in a well-organized form.
Part D Problem Requirements
To organize the views of Wild Outfitters data and present it in a well-organized form, Isaac will use the MS Access Report object. A report can be based on a table or a query or a combination of tables and queries; however, for Part D of this project you need only to create a report based on Query 2 from Part C above. You can use the Report Wizard to create your report. The report should show all of the data contained in Query 2. When responding to the Report Wizard’s prompts, you should select to view your data by class, add “class date” as grouping level, and sort the detailed records by customer last name in ascending order. The layout and style choices are up to you. Finish the report and name it septemberQuery.
Review your report and note the “database speak” used for the title and headings. Since the purpose of a well-crafted report is to convey useful information effectively and efficiently, you will need to edit your report. Here are the minimum requirements for Part D of the project.
|
1. Change the orientation of the report from portrait to landscape. 2. Change the title of the report to “Wild Outfitters September 2008 Classes.” 3. Move the customer first name label and data so that they are next to each other, last name then first name. 4. Transform all field names into easily readable headings; for example, CourseName should become Course Name, CourseType should become Course Type, and so forth. |
Your instructor may add additional requirements or you may want to explore just how professional you can make the report appear.
|
Part D Interpretive Questions Based on your work in Part D, answer the following questions: 1. What other reports would you suggest that Isaac should create for Ms. Johns to help her understand and manage the courses and classes that Wild Outfitters provides to its customers? 2. We have mentioned several Access objects throughout this project. Did you notice any additional objects when you were working in the Design view? What were some of these objects, and how did you know that you were looking at an object? |
ACC_2_Bank_Database_Example.accdb
LoanID LoanType LoanRate LoanMin
1 15 Year Mortgage 0.045 ¤ 50,000.00
2 30 Year Mortgage 0.055 ¤ 75,000.00
3 Auto Loan 36-Month 0.085 ¤ 5,000.00
4 Auto Loan 48-Month 0.095 ¤ 7,500.00
5 Auto Loan 60-Month 0.105 ¤ 10,000.00
6 Student 0.05 ¤ 2,000.00
SELECT DISTINCTROW *
FROM loans;
ACC_2_MC_Employees.accdb
empID empFirstName empLastName empDepartment empStartDate empBirthDate empSalary
1 John Wise ACC 8/11/04 5/10/83 ¤ 30,500.00
2 Joel Williams MIS 6/2/88 1/17/50 ¤ 120,000.00
3 Bryan Fowler ACC 3/3/01 9/9/80 ¤ 34,000.00
4 David Jackson FIN 6/13/88 10/5/50 ¤ 105,300.00
5 Jesse White MKT 3/4/97 6/27/70 ¤ 48,300.00
6 William Fain MKT 4/17/97 4/4/74 ¤ 45,600.00
7 Lindsay Spears OPS 11/22/99 3/17/78 ¤ 39,000.00
8 Kenneth Wetmore MIS 3/27/01 2/4/81 ¤ 33,250.00
9 Christina Kannan ACC 7/17/98 11/22/77 ¤ 39,000.00
10 Justin Ellis MKT 5/26/97 1/15/76 ¤ 44,500.00
11 John Welch OPS 4/18/03 5/11/82 ¤ 32,400.00
12 Patrick Lane HRS 10/28/96 5/22/70 ¤ 49,320.00
13 William Davis MIS 4/21/04 9/16/82 ¤ 31,300.00
14 Benjamin Tripp ACC 2/3/96 1/25/64 ¤ 53,400.00
15 Stephanie Mane OPS 9/19/01 9/4/81 ¤ 32,450.00
16 Seth Cook ACC 3/14/96 1/13/67 ¤ 53,200.00
17 Brenton Timmins MKT 9/17/90 3/31/54 ¤ 81,650.00
18 Toby Childers HRE 1/14/92 6/27/55 ¤ 76,840.00
19 Adam Burns FIN 2/18/95 7/18/62 ¤ 58,250.00
20 Jason Thomas OPS 5/17/93 5/12/57 ¤ 71,340.00
21 Wilson Marceau ACC 11/2/94 11/23/59 ¤ 63,250.00
22 Thomas Strength OPS 12/8/93 9/26/57 ¤ 66,500.00
23 Kristin Bryan MKT 7/19/93 9/8/57 ¤ 68,400.00
24 Campbell Smith OPS 11/11/94 6/18/62 ¤ 61,450.00
25 William Marshall MIS 1/12/91 5/4/54 ¤ 80,650.00
26 David Michaels ACC 6/1/95 9/10/62 ¤ 56,850.00
27 Charles Bowen MKT 10/13/92 2/12/57 ¤ 73,500.00
28 Kevin Blackburn ACC 7/25/96 8/17/68 ¤ 51,250.00
29 Deitz Mierke OPS 7/15/88 4/23/52 ¤ 96,750.00
30 Samuel Serrano OPS 3/18/97 5/26/72 ¤ 46,300.00
31 Richard Barattini HRE 6/21/98 11/15/77 ¤ 39,500.00
32 Viji Schernakau ACC 8/21/91 7/4/54 ¤ 78,450.00
33 Kacy McCullers OPS 5/1/98 9/1/77 ¤ 41,320.00
34 Moussa Reid FIN 6/6/90 1/12/54 ¤ 83,250.00
35 Jessica May MKT 5/13/88 3/4/48 ¤ 145,300.00
36 Ashley Nelkin ACC 9/24/94 8/15/58 ¤ 65,400.00
37 Adam McPeters OPS 2/3/89 8/12/53 ¤ 88,300.00
38 Tommy Orr MIS 2/25/94 10/26/57 ¤ 65,500.00
39 Heather Newland ACC 1/27/01 8/25/80 ¤ 36,500.00
40 Nicolas Alexander MIS 7/18/88 10/20/52 ¤ 93,200.00
ACC_4_MC_LinkSaver.accdb
LID LTitle LURL NumLinks LinkAddDate
1 Google Search Engine www.google.com 2 4/7/07
2 Yahoo! www.yahoo.com 2 4/6/07
4 CNN News Network www.cnn.com 1 4/3/07
5 University of Georgia www.uga.edu 1 4/10/07
7 Official ASP.Net Site www.asp.net 2 4/27/07
LUID fkUID fkLID LCat LDate
1 1 1 Search 4/7/07
2 1 2 Search 4/6/07
4 2 1 Search Engines 4/20/07
5 3 2 Search Sites 4/27/07
6 2 4 News 4/3/07
7 3 5 School 4/10/07
11 2 7 Programming 4/28/07
12 1 7 Web Development 4/27/07
UID LUserName LPassword LEmail
1 cpearson sd123$ cpearson@ga.edu
2 kilgore j$k$L$ ktrout@aol.com
3 Adent a2b9c2 Adent@hotmail.com
4 nick dnao89s nick@aol.com
5 estelle 98jk12$ estelle@aol.com
6 jim oskd49 jim@hotmail.com
SELECT tblUserLink.fkUID, tblLink.LID
FROM tblLink INNER JOIN tblUserLink ON tblLink.LID=tblUserLink.fkLID
WHERE (((tblUserLink.fkUID)=1));
SELECT tblUserLink.LDate, tblLink.LTitle, tblLink.LURL
FROM tblLink INNER JOIN tblUserLink ON tblLink.LID=tblUserLink.fkLID
WHERE (((tblUserLink.LCat)=”Search”) AND ((tblUserLink.fkUID)=1));
ACC_4_MC_Students.accdb
CurrentMajorID StudentID MajorID Date Enrolled
1 1 1 1/1/00
2 2 2 1/2/00
3 3 3 1/3/00
4 4 4 1/4/00
5 5 5 1/5/00
6 6 6 1/6/00
7 7 7 1/7/00
8 8 8 1/8/00
9 9 9 1/9/00
10 10 10 1/10/00
11 11 11 1/11/00
12 12 12 1/12/00
13 13 13 1/13/00
14 14 14 1/14/00
15 15 15 1/15/00
16 16 16 1/16/00
17 17 17 1/17/00
18 18 18 1/18/00
19 19 19 1/19/00
20 20 20 1/20/00
21 21 21 1/21/00
22 22 22 1/22/00
23 23 23 1/23/00
24 24 24 1/24/00
25 25 25 1/25/00
26 26 26 1/26/00
27 27 27 1/27/00
28 28 28 1/28/00
29 29 29 1/29/00
30 30 30 1/30/00
31 31 31 1/31/00
32 32 32 2/1/00
33 33 33 2/2/00
34 34 34 2/3/00
35 35 35 2/4/00
36 36 36 2/5/00
37 37 37 2/6/00
38 38 38 2/7/00
39 39 39 2/8/00
40 40 40 2/9/00
41 41 41 2/10/00
42 42 42 2/11/00
43 43 43 2/12/00
44 44 44 2/13/00
45 45 45 2/14/00
46 46 46 2/15/00
47 47 47 2/16/00
48 48 48 2/17/00
49 49 49 2/18/00
50 50 50 2/19/00
51 51 51 2/20/00
52 52 52 2/21/00
53 53 53 2/22/00
54 54 54 2/23/00
55 55 55 2/24/00
56 56 56 2/25/00
57 57 57 2/26/00
58 58 58 2/27/00
59 59 59 2/28/00
60 60 60 2/29/00
61 61 61 3/1/00
62 62 62 3/2/00
63 63 63 3/3/00
64 64 64 3/4/00
65 65 65 3/5/00
66 66 66 3/6/00
67 67 67 3/7/00
68 68 68 3/8/00
69 69 69 3/9/00
70 70 70 3/10/00
71 71 71 3/11/00
72 72 72 3/12/00
73 73 73 3/13/00
74 74 74 3/14/00
75 75 75 3/15/00
76 76 76 3/16/00
77 77 77 3/17/00
78 78 78 3/18/00
79 79 79 3/19/00
80 80 80 3/20/00
81 81 81 3/21/00
82 82 82 3/22/00
83 83 83 3/23/00
84 84 84 3/24/00
85 85 85 3/25/00
86 86 86 3/26/00
87 87 87 3/27/00
88 88 88 3/28/00
89 89 89 3/29/00
90 90 90 3/30/00
91 91 91 3/31/00
92 92 92 4/1/00
93 93 93 4/2/00
94 94 94 4/3/00
95 95 95 4/4/00
96 96 96 4/5/00
97 97 97 4/6/00
98 98 98 4/7/00
99 99 99 4/8/00
100 100 100 4/9/00
101 101 101 4/10/00
102 102 102 4/11/00
103 103 103 4/12/00
104 104 104 4/13/00
105 105 105 4/14/00
106 106 106 4/15/00
107 107 107 4/16/00
108 108 108 4/17/00
109 109 109 4/18/00
110 110 110 4/19/00
111 111 111 4/20/00
112 112 112 4/21/00
113 113 113 4/22/00
114 114 114 4/23/00
115 115 115 4/24/00
116 116 116 4/25/00
117 117 117 4/26/00
118 118 118 4/27/00
119 119 119 4/28/00
120 120 120 4/29/00
121 121 121 4/30/00
122 122 122 5/1/00
123 123 123 5/2/00
124 124 1 1/1/00
125 125 2 1/2/00
126 126 3 1/3/00
127 127 4 1/4/00
128 128 5 1/5/00
129 129 6 1/6/00
130 130 7 1/7/00
131 131 8 1/8/00
132 132 9 1/9/00
133 133 10 1/10/00
134 134 11 1/11/00
135 135 12 1/12/00
136 136 13 1/13/00
137 137 14 1/14/00
138 138 15 1/15/00
139 139 16 1/16/00
140 140 17 1/17/00
141 141 18 1/18/00
142 142 19 1/19/00
143 143 20 1/20/00
144 144 21 1/21/00
145 145 22 1/22/00
146 146 23 1/23/00
147 147 24 1/24/00
148 148 25 1/25/00
149 149 26 1/26/00
150 150 27 1/27/00
151 151 28 1/28/00
152 152 29 1/29/00
153 153 30 1/30/00
154 154 31 1/31/00
155 155 32 2/1/00
156 156 33 2/2/00
157 157 34 2/3/00
158 158 35 2/4/00
159 159 36 2/5/00
160 160 37 2/6/00
161 161 38 2/7/00
162 162 39 2/8/00
163 163 40 2/9/00
164 164 41 2/10/00
165 165 42 2/11/00
166 166 43 2/12/00
167 167 44 2/13/00
168 168 45 2/14/00
169 169 46 2/15/00
170 170 47 2/16/00
171 171 48 2/17/00
172 172 49 2/18/00
173 173 50 2/19/00
174 174 51 2/20/00
175 175 52 2/21/00
176 176 53 2/22/00
177 177 54 2/23/00
178 178 55 2/24/00
179 179 56 2/25/00
180 180 57 2/26/00
181 181 58 2/27/00
182 182 59 2/28/00
183 183 60 2/29/00
184 184 61 3/1/00
185 185 62 3/2/00
186 186 63 3/3/00
187 187 64 3/4/00
188 188 65 3/5/00
189 189 66 3/6/00
190 190 67 3/7/00
191 191 68 3/8/00
192 192 69 3/9/00
193 193 70 3/10/00
194 194 71 3/11/00
195 195 72 3/12/00
196 196 73 3/13/00
197 197 74 3/14/00
198 198 75 3/15/00
199 199 76 3/16/00
200 200 77 3/17/00
201 201 78 3/18/00
202 202 79 3/19/00
203 203 80 3/20/00
204 204 81 3/21/00
205 205 82 3/22/00
206 206 83 3/23/00
207 207 84 3/24/00
208 208 85 3/25/00
209 209 86 3/26/00
210 210 87 3/27/00
211 211 88 3/28/00
212 212 89 3/29/00
213 213 90 3/30/00
214 214 91 3/31/00
215 215 92 4/1/00
216 216 93 4/2/00
217 217 94 4/3/00
218 218 95 4/4/00
219 219 96 4/5/00
220 220 97 4/6/00
221 221 98 4/7/00
222 222 99 4/8/00
223 223 100 4/9/00
224 224 101 4/10/00
225 225 102 4/11/00
226 226 103 4/12/00
227 227 104 4/13/00
228 228 105 4/14/00
229 229 106 4/15/00
230 230 107 4/16/00
231 231 108 4/17/00
232 232 109 4/18/00
233 233 110 4/19/00
234 234 111 4/20/00
235 235 112 4/21/00
236 236 113 4/22/00
237 237 114 4/23/00
238 238 115 4/24/00
239 239 116 4/25/00
240 240 117 4/26/00
241 241 118 4/27/00
242 242 119 4/28/00
243 243 120 4/29/00
244 244 121 4/30/00
245 245 122 5/1/00
246 246 123 5/2/00
247 247 1 1/1/00
248 248 2 1/2/00
249 249 3 1/3/00
250 250 4 1/4/00
251 251 5 1/5/00
252 252 6 1/6/00
253 253 7 1/7/00
254 254 8 1/8/00
255 255 9 1/9/00
256 256 10 1/10/00
257 257 11 1/11/00
258 258 12 1/12/00
259 259 13 1/13/00
260 260 14 1/14/00
261 261 15 1/15/00
262 262 16 1/16/00
263 263 17 1/17/00
264 264 18 1/18/00
265 265 19 1/19/00
266 266 20 1/20/00
267 267 21 1/21/00
268 268 22 1/22/00
269 269 23 1/23/00
270 270 24 1/24/00
271 271 25 1/25/00
272 272 26 1/26/00
273 273 27 1/27/00
274 274 28 1/28/00
275 275 29 1/29/00
276 276 30 1/30/00
277 277 31 1/31/00
278 278 32 2/1/00
279 279 33 2/2/00
280 280 34 2/3/00
281 281 35 2/4/00
282 282 36 2/5/00
283 283 37 2/6/00
284 284 38 2/7/00
285 285 39 2/8/00
286 286 40 2/9/00
287 287 41 2/10/00
288 288 42 2/11/00
289 289 43 2/12/00
290 290 44 2/13/00
291 291 45 2/14/00
292 292 46 2/15/00
293 293 47 2/16/00
294 294 48 2/17/00
295 295 49 2/18/00
296 296 50 2/19/00
297 297 51 2/20/00
298 298 52 2/21/00
299 299 53 2/22/00
300 300 54 2/23/00
301 301 55 2/24/00
302 302 56 2/25/00
303 303 57 2/26/00
304 304 58 2/27/00
305 305 59 2/28/00
306 306 60 2/29/00
307 307 61 3/1/00
308 308 62 3/2/00
309 309 63 3/3/00
310 310 64 3/4/00
311 311 65 3/5/00
312 312 66 3/6/00
313 313 67 3/7/00
314 314 68 3/8/00
315 315 69 3/9/00
316 316 70 3/10/00
317 317 71 3/11/00
318 318 72 3/12/00
319 319 73 3/13/00
320 320 74 3/14/00
321 321 75 3/15/00
322 322 76 3/16/00
323 323 77 3/17/00
324 324 78 3/18/00
325 325 79 3/19/00
326 326 80 3/20/00
327 327 81 3/21/00
328 328 82 3/22/00
329 329 83 3/23/00
330 330 84 3/24/00
331 331 85 3/25/00
332 332 86 3/26/00
333 333 87 3/27/00
334 334 88 3/28/00
335 335 89 3/29/00
336 336 90 3/30/00
337 337 91 3/31/00
338 338 92 4/1/00
339 339 93 4/2/00
340 340 94 4/3/00
341 341 95 4/4/00
342 342 96 4/5/00
343 343 97 4/6/00
344 344 98 4/7/00
345 345 99 4/8/00
346 346 100 4/9/00
347 347 101 4/10/00
348 348 102 4/11/00
349 349 103 4/12/00
350 350 104 4/13/00
351 351 105 4/14/00
352 352 106 4/15/00
353 353 107 4/16/00
354 354 108 4/17/00
355 355 109 4/18/00
356 356 110 4/19/00
357 357 111 4/20/00
358 358 112 4/21/00
359 359 113 4/22/00
360 360 114 4/23/00
361 361 115 4/24/00
362 362 116 4/25/00
363 363 117 4/26/00
364 364 118 4/27/00
365 365 119 4/28/00
366 366 120 4/29/00
367 367 121 4/30/00
368 368 122 5/1/00
369 369 123 5/2/00
370 370 1 1/1/00
371 371 2 1/2/00
372 372 3 1/3/00
373 373 4 1/4/00
374 374 5 1/5/00
375 375 6 1/6/00
376 376 7 1/7/00
377 377 8 1/8/00
378 378 9 1/9/00
379 379 10 1/10/00
380 380 11 1/11/00
381 381 12 1/12/00
382 382 13 1/13/00
383 383 14 1/14/00
384 384 15 1/15/00
385 385 16 1/16/00
386 386 17 1/17/00
387 387 18 1/18/00
388 388 19 1/19/00
389 389 20 1/20/00
390 390 21 1/21/00
391 391 22 1/22/00
392 392 23 1/23/00
393 393 24 1/24/00
394 394 25 1/25/00
395 395 26 1/26/00
396 396 27 1/27/00
397 397 28 1/28/00
398 398 29 1/29/00
399 399 30 1/30/00
400 400 31 1/31/00
401 401 32 2/1/00
402 402 33 2/2/00
403 403 34 2/3/00
404 404 35 2/4/00
405 405 36 2/5/00
406 406 37 2/6/00
407 407 38 2/7/00
408 408 39 2/8/00
409 409 40 2/9/00
410 410 41 2/10/00
411 411 42 2/11/00
412 412 43 2/12/00
413 413 44 2/13/00
414 414 45 2/14/00
415 415 46 2/15/00
416 416 47 2/16/00
417 417 48 2/17/00
418 418 49 2/18/00
419 419 50 2/19/00
420 420 51 2/20/00
421 421 52 2/21/00
422 422 53 2/22/00
423 423 54 2/23/00
424 424 55 2/24/00
425 425 56 2/25/00
426 426 57 2/26/00
427 427 58 2/27/00
428 428 59 2/28/00
429 429 60 2/29/00
430 430 61 3/1/00
431 431 62 3/2/00
432 432 63 3/3/00
433 433 64 3/4/00
434 434 65 3/5/00
435 435 66 3/6/00
436 436 67 3/7/00
437 437 68 3/8/00
438 438 69 3/9/00
439 439 70 3/10/00
440 440 71 3/11/00
441 441 72 3/12/00
442 442 73 3/13/00
443 443 74 3/14/00
444 444 75 3/15/00
445 445 76 3/16/00
446 446 77 3/17/00
447 447 78 3/18/00
448 448 79 3/19/00
449 449 80 3/20/00
450 450 81 3/21/00
451 451 82 3/22/00
452 452 83 3/23/00
453 453 84 3/24/00
454 454 85 3/25/00
455 455 86 3/26/00
456 456 87 3/27/00
457 457 88 3/28/00
458 458 89 3/29/00
459 459 90 3/30/00
460 460 91 3/31/00
461 461 92 4/1/00
462 462 93 4/2/00
463 463 94 4/3/00
464 464 95 4/4/00
465 465 96 4/5/00
466 466 97 4/6/00
467 467 98 4/7/00
468 468 99 4/8/00
469 469 100 4/9/00
470 470 101 4/10/00
471 471 102 4/11/00
472 472 103 4/12/00
473 473 104 4/13/00
474 474 105 4/14/00
475 475 106 4/15/00
476 476 107 4/16/00
477 477 108 4/17/00
478 478 109 4/18/00
479 479 110 4/19/00
480 480 111 4/20/00
481 481 112 4/21/00
482 482 113 4/22/00
483 483 114 4/23/00
484 484 115 4/24/00
485 485 116 4/25/00
486 486 117 4/26/00
487 487 118 4/27/00
488 488 119 4/28/00
489 489 120 4/29/00
490 490 121 4/30/00
491 491 122 5/1/00
492 492 123 5/2/00
493 493 1 1/1/00
494 494 2 1/2/00
495 495 3 1/3/00
496 496 4 1/4/00
497 497 5 1/5/00
498 498 6 1/6/00
499 499 7 1/7/00
500 500 8 1/8/00
501 501 9 1/9/00
502 502 10 1/10/00
503 503 11 1/11/00
504 504 12 1/12/00
505 505 13 1/13/00
506 506 14 1/14/00
507 507 15 1/15/00
508 508 16 1/16/00
509 509 17 1/17/00
510 510 18 1/18/00
511 511 19 1/19/00
512 512 20 1/20/00
513 513 21 1/21/00
514 514 22 1/22/00
515 515 23 1/23/00
516 516 24 1/24/00
517 517 25 1/25/00
518 518 26 1/26/00
519 519 27 1/27/00
520 520 28 1/28/00
521 521 29 1/29/00
522 522 30 1/30/00
523 523 31 1/31/00
524 524 32 2/1/00
525 525 33 2/2/00
526 526 34 2/3/00
527 527 35 2/4/00
528 528 36 2/5/00
529 529 37 2/6/00
530 530 38 2/7/00
531 531 39 2/8/00
532 532 40 2/9/00
533 533 41 2/10/00
534 534 42 2/11/00
535 535 43 2/12/00
536 536 44 2/13/00
537 537 45 2/14/00
538 538 46 2/15/00
539 539 47 2/16/00
540 540 48 2/17/00
541 541 49 2/18/00
542 542 50 2/19/00
543 543 51 2/20/00
544 544 52 2/21/00
545 545 53 2/22/00
546 546 54 2/23/00
547 547 55 2/24/00
548 548 56 2/25/00
549 549 57 2/26/00
550 550 58 2/27/00
551 551 59 2/28/00
552 552 60 2/29/00
553 553 61 3/1/00
MajorID Major MajorAbbreviation
1 ACCOUNTING ACCT ACCT
2 ADV AND PUBLIC RELATIONS ADPR ADPR
3 AEROSPACE STUDIES AIRS AIRS
4 AFRICAN-AMERICAN STUDIES AFAM AFAM
5 AG LEADERSHIP, ED & COMM ALDR ALDR
6 AGR & APPLIED ECONOMICS AAEC AAEC
7 AGR & ENVIRONMENTAL SCI AESC AESC
8 AGRICULTURAL EDUCATION EAGR EAGR
9 ANIMAL & DAIRY SCIENCE ADSC ADSC
10 ANTHROPOLOGY ANTH ANTH
11 ARABIC ARAB ARAB
12 ARMY ROTC – MILITARY SCI MILS MILS
13 ART ARTS ARTS
14 ART EDUCATION ARED ARED
15 ART GRAPHIC DESIGN ARGD ARGD
16 ART HISTORY ARHI ARHI
17 ART INTERIOR DESIGN ARID ARID
18 ARTIFICIAL INTELLIGENCE ARTI ARTI
19 ASTRONOMY ASTR ASTR
20 BANKING & FINANCE FINA FINA
21 BIOCHEM & MOLECULAR BIO BCMB BCMB
22 BIOLOGY BIOL BIOL
23 BOTANY BTNY BTNY
24 BUSINESS EDUCATION EBUS EBUS
25 CELLULAR BIOLOGY CBIO CBIO
26 CHEMISTRY CHEM CHEM
27 CHILD & FAMILY DEVELOPMENT CHFD CHFD
28 CHINESE CHNS CHNS
29 CLASSICAL CULTURE CLAS CLAS
30 COMMUNICATION SCI & DISORD CMSD CMSD
31 COMPARATIVE LITERATURE CMLT CMLT
32 COMPUTER SCIENCE CSCI CSCI
33 COUNSELING & PERSONNEL SER ECHD ECHD
34 CROP & SOIL SCIENCES CRSS CRSS
35 DANCE EDUCATION DANC DANC
36 DRAMA DRAM DRAM
37 EARLY CHILDHOOD EDUCATION EDEC EDEC
38 ECOLOGY ECOL ECOL
39 ECONOMICS ECON ECON
40 EDUC RSCH & MEASUREMENTS ERSH ERSH
41 EDUCATIONAL PSYCHOLOGY EPSY EPSY
42 ENGINEERING ENGR ENGR
43 ENGLISH ENGL ENGL
44 ENTOMOLOGY ENTO ENTO
45 ENVIRONMENTAL DESIGN EDES EDES
46 ENVIRONMENTAL ETHICS EETH EETH
47 ENVIRONMENTAL HEALTH SCI EHSC EHSC
48 EXERCISE SCIENCE EXRS EXRS
49 FOOD SCIENCE & TECHNOLOGY FDST FDST
50 FOODS & NUTRITION FDNS FDNS
51 FOREST RESOURCES FORS FORS
52 FOUNDATIONS IN EDUCATION EFND EFND
53 FRENCH FREN FREN
54 FRESHMAN SEMINARS FRES FRES
55 GENETICS GENE GENE
56 GEOGRAPHY GEOG GEOG
57 GEOLOGY GEOL GEOL
58 GERMAN GRMN GRMN
59 GREEK GREK GREK
60 HEALTH PROMOTION/BEHAVIOR HPRB HPRB
61 HEBREW HEBR HEBR
62 HINDI HNDI HNDI
63 HISTORY HIST HIST
64 HONORS PROGRAM HONS HONS
65 HORTICULTURE HORT HORT
66 HOUSING & CONSUMER ECON HACE HACE
67 INSTRUCTIONAL TECHNOLOGY EDIT EDIT
68 ITALIAN ITAL ITAL
69 JAPANESE JPNS JPNS
70 JOURNALISM JOUR JOUR
71 JOURNALISM CORE JRLC JRLC
72 KOREAN KREN KREN
73 LANDSCAPE ARCHITECTURE LAND LAND
74 LANGUAGE EDUCATION ELAN ELAN
75 LATIN LATN LATN
76 LEGAL STUDIES LEGL LEGL
77 LINGUISTICS LING LING
78 MANAGEMENT MGMT MGMT
79 MANAGEMENT INFO SYSTEMS MIST MIST
80 MANAGEMENT SCIENCES MSIT MSIT
81 MARINE SCIENCES MARS MARS
82 MARKETING MARK MARK
83 MARKETING EDUCATION EMKT EMKT
84 MATHEMATICS MATH MATH
85 MATHEMATICS EDUCATION EMAT EMAT
86 MICROBIOLOGY MIBO MIBO
87 MIDDLE SCHOOL EDUCATION EDMS EDMS
88 MUSIC MUSI MUSI
89 OCCUPATIONAL STUDIES EOCS EOCS
90 PHARMACY PHAR PHAR
91 PHARMACY PHRM PHRM
92 PHILOSOPHY PHIL PHIL
93 PHYS EDU & SPORT STUDIES PEDS PEDS
94 PHYSICS PHYS PHYS
95 PLANT GENETICS PGEN PGEN
96 PLANT PATHOLOGY PATH PATH
97 POLITICAL SCIENCE POLS POLS
98 PORTUGUESE PORT PORT
99 POULTRY SCIENCE POUL POUL
100 PSYCHOLOGY PSYC PSYC
101 READING EDUCATION READ READ
102 REAL ESTATE REAL REAL
103 RECREATION & LEISURE STUDY RLST RLST
104 RELIGION RELI RELI
105 RISK MANAGEMT & INSURANCE RMIN RMIN
106 ROMANCE LANGUAGES ROML ROML
107 RUSSIAN RUSS RUSS
108 SCIENCE EDUCATION ESCI ESCI
109 SOCIAL SCIENCE EDUCATION ESOC ESOC
110 SOCIAL WORK SOWK SOWK
111 SOCIOLOGY SOCI SOCI
112 SPANISH SPAN SPAN
113 SPECIAL EDUCATION SPED SPED
114 SPEECH COMMUNICATION SPCM SPCM
115 STATISTICS STAT STAT
116 STUDIO ART ARST ARST
117 SWAHILI SWAH SWAH
118 TECHNOLOGICAL STUDIES ETES ETES
119 TELECOMMUNICATIONS TELE TELE
120 TEXTILES, MERCH & INTERIOR TXMI TXMI
121 UNIVERSITY ORIENTATION UNIV UNIV
122 WOMEN’S STUDIES WMST WMST
123 YORUBA YORB YORB
StudentID LastName FirstName Age BirthDayOfWeek
1 ABSTON AARON 37.0 Sunday
2 ACKERMANN ADAM 25.0 Monday
3 ADAMS ADAM 48.0 Tuesday
4 ADCOCK ADAUGO 43.0 Wednesday
5 ADROVER AIMEE 57.0 Thursday
6 AHMED ALAINA 51.0 Friday
7 AKLY ALAN 31.0 Saturday
8 ALABI ALDREQ 43.0 Sunday
9 ALEXANDER ALENDA 48.0 Monday
10 ALLEN ALEX 55.0 Tuesday
11 ALLEN ALEXIS 29.0 Wednesday
12 ALLEN ALLISON 47.0 Thursday
13 ALLERDICE ALLISON 28.0 Friday
14 ALLGOOD ALLISON 52.0 Saturday
15 ALVAREZ AMY 26.0 Sunday
16 AMOSS AMANDA 44.0 Monday
17 ANDERSON AMANDA 33.0 Tuesday
18 ANDERSON AMANDA 41.0 Wednesday
19 ANDREWS AMANDA 18.0 Thursday
20 ARBUCKLE AMANDA 26.0 Friday
21 ARNOLD AMANDA 56.0 Saturday
22 ARNOLD AMANDA 17.0 Sunday
23 ASAFO AMANDA 34.0 Monday
24 ASSADI AMBER 48.0 Tuesday
25 AUSTIN AMBER 30.0 Wednesday
26 BACHMAN AMY 29.0 Thursday
27 BAINE AMY 26.0 Friday
28 BAKER ANDREA 19.0 Saturday
29 BAKER ANDREA 57.0 Sunday
30 BAKER ANDREW 37.0 Monday
31 BALDWIN ANDREW 17.0 Tuesday
32 BAN ANDREW 39.0 Wednesday
33 BANKS ANDREW 17.0 Thursday
34 BARBAREE ANDREW 57.0 Friday
35 BARNABY ANDREW 18.0 Saturday
36 BAUER ANDREW 52.0 Sunday
37 BAUMAN ANDREW 56.0 Monday
38 BAUMAN ANDY 47.0 Tuesday
39 BAUMGARTNER ANEHIT 58.0 Wednesday
40 BAXTER ANGELA 51.0 Thursday
41 BECKHAM ANGELA 55.0 Friday
42 BEGNAUD ANGELA 30.0 Saturday
43 BELL ANNA 58.0 Sunday
44 BELL ANNA 26.0 Monday
45 BENEDICT ANNE 28.0 Tuesday
46 BENNETT ANNE 32.0 Wednesday
47 BITER ANNIE 43.0 Thursday
48 BLANCHETTE APRIL 38.0 Friday
49 BLAZE APRIL 41.0 Saturday
50 BLONDEAU APRIL 54.0 Sunday
51 BLOOM APRIL 34.0 Monday
52 BOEKER ASHLEY 58.0 Tuesday
53 BOEKER ASHLEY 60.0 Wednesday
54 BOLDEN ASHLEY 24.0 Thursday
55 BONE ASHLEY 54.0 Friday
56 BORENSTEIN ASHLEY 54.0 Saturday
57 BOSSHARDT ASHLEY 35.0 Sunday
58 BQCK ASHLEY 22.0 Monday
59 BRADFORD ASHLEY 47.0 Tuesday
60 BRANAN ASHLEY 53.0 Wednesday
61 BRANNEN AUSTIN 29.0 Thursday
62 BREEDLOVE BARRET 50.0 Friday
63 BRENNAN BARRET 20.0 Saturday
64 BREYNE BEAU 32.0 Sunday
65 BRIGGS BEN 53.0 Monday
66 BRINER BILLY 49.0 Tuesday
67 BRISCOE BLAIR 17.0 Wednesday
68 BROBBEY BLAKE 23.0 Thursday
69 BRODER BLAKE 33.0 Friday
70 BROOKS BLANE 32.0 Saturday
71 BROWN BLANTON 30.0 Sunday
72 BROWN BOBBY 25.0 Monday
73 BROWN BRAD 48.0 Tuesday
74 BRYANT BRAD 60.0 Wednesday
75 BRYSON BRAD 45.0 Thursday
76 BULGER BRENT 27.0 Friday
77 BUNCHMAN BRET 49.0 Saturday
78 BURNETT BRETT 57.0 Sunday
79 BURNS BRETT 54.0 Monday
80 BY BRIAN 25.0 Tuesday
81 CAFTARI BRIAN 27.0 Wednesday
82 CAGLE BRIAN 28.0 Thursday
83 CALDWELL BRIAN 57.0 Friday
84 CAMPBELL BRIAN 45.0 Saturday
85 CAMPBELL BRIAN 55.0 Sunday
86 CAMPIONE BROOKE 26.0 Monday
87 CARLSON BRYAN 17.0 Tuesday
88 CARPENTER BRYAN 18.0 Wednesday
89 CARTER CAITLIN 56.0 Thursday
90 CASE CANDICE 22.0 Friday
91 CASSELL CAP 53.0 Saturday
92 CATHEY CARISSA 24.0 Sunday
93 CATONE CARLA 21.0 Monday
94 CAVANAUGH CARLOS 51.0 Tuesday
95 CAWLEY CAROLINE 19.0 Wednesday
96 CHAMBERS CARRIE 42.0 Thursday
97 CHAMBERS CARRIE 40.0 Friday
98 CHAPMAN CASEY 51.0 Saturday
99 CHESSER CHAN 51.0 Sunday
100 CHRISTIANSEN CHARLES 39.0 Monday
101 CLANCY CHARLES 50.0 Tuesday
102 CLAPP CHARLES 55.0 Wednesday
103 CLAR K CHARLES 56.0 Thursday
104 CLINTON CHRIS 51.0 Friday
105 COGGINS CHRIS 38.0 Saturday
106 COHEN CHRIS 48.0 Sunday
107 COKER CHRIS 31.0 Monday
108 COLBERT CHRIS 19.0 Tuesday
109 COLCLASURE CHRIS 30.0 Wednesday
110 COLETTI CHRIS 27.0 Thursday
111 COLLINS CHRIS 29.0 Friday
112 COLLINS CHRIS 58.0 Saturday
113 COLONDRES CHRIS 32.0 Sunday
114 CONNER CHRIS 27.0 Monday
115 CONROY CHRIS 18.0 Tuesday
116 CONWAY CHRIS 43.0 Wednesday
117 COTTINGHAM CHRIS 37.0 Thursday
118 COX CHRIS 30.0 Friday
119 CRAWLEY CLARE 47.0 Saturday
120 CROMLEY CLAUDIA 49.0 Sunday
121 CROWLEY CLAUDIA 47.0 Monday
122 CRYANT CLINT 18.0 Tuesday
123 CRYSTAL COLLIN 19.0 Wednesday
124 CUCUZZA COURTNEY 29.0 Thursday
125 CUDNIK COURTNEY 20.0 Friday
126 CULL COURTNEY 57.0 Saturday
127 CULPEPPER COURTNEY 47.0 Sunday
128 CURRY COURTNEY 32.0 Monday
129 DARRISH COURTNEY 22.0 Tuesday
130 DAUGHERTY DALE 57.0 Wednesday
131 DAVIDSON DANIEL 55.0 Thursday
132 DAVIS DANIEL 41.0 Friday
133 DAY DANIEL 28.0 Saturday
134 DAY DANIEL 23.0 Sunday
135 DAY DANIEL 60.0 Monday
136 DAYE DANIEL 44.0 Tuesday
137 DEAN DARON 48.0 Wednesday
138 DEANER DARRYL 43.0 Thursday
139 DEGANCE DAVID 35.0 Friday
140 DELONG DAVID 24.0 Saturday
141 DEMPSEY DAVID 20.0 Sunday
142 DEROSA DAVID 34.0 Monday
143 DICKSON DAVID 29.0 Tuesday
144 DIDRIKSDOTTIR DAVID 44.0 Wednesday
145 DILLARD OSEP DAVID 41.0 Thursday
146 DIXON DAVID 53.0 Friday
147 DIXON DEBORA 27.0 Saturday
148 DOLCIMASCOLO DEBORA 51.0 Sunday
149 DOMINGUEZ DENNIS 35.0 Monday
150 DOSS DEREK 29.0 Tuesday
151 DOUGHT Y DEREK 50.0 Wednesday
152 DOUGLAS DUSTY 52.0 Thursday
153 DREW EDDIE 29.0 Friday
154 DSOUZA EDWARD 20.0 Saturday
155 DUKE ELEANO 58.0 Sunday
156 DUNN ELIZABETH 42.0 Monday
157 DURHAM ELIZABETH 56.0 Tuesday
158 EASON ELIZABETH 23.0 Wednesday
159 EATON ELIZABETH 27.0 Thursday
160 EGOSI ELIZABETH 42.0 Friday
161 EIGBE ELIZABETH 57.0 Saturday
162 ELDRIDGE EMILY 48.0 Sunday
163 ELIAS EMILY 29.0 Monday
164 ELSBERRY EMILY 48.0 Tuesday
165 ENGEL EMILY 27.0 Wednesday
166 EPSTEIN EMMY 36.0 Thursday
167 EURE ENIOLA 33.0 Friday
168 EVANS ERIC 43.0 Saturday
169 EVANS ERIC 29.0 Sunday
170 EVANS ERIC 25.0 Monday
171 EZELL ERIC 55.0 Tuesday
172 FARIA ERICA 17.0 Wednesday
173 FEIGHNER ERIK 35.0 Thursday
174 FELKER ERIK 59.0 Friday
175 FERRANTE ERIN 30.0 Saturday
176 FESSLER ERIN 47.0 Sunday
177 FISTER ERIN 31.0 Monday
178 FITZPATRICK ERIN 25.0 Tuesday
179 FLEMING EVAN 49.0 Wednesday
180 FLIPPEN FELIX 24.0 Thursday
181 FOLDS FRED 60.0 Friday
182 FORLINES FRED 38.0 Saturday
183 FOSTER GARY 25.0 Sunday
184 FOWLER GEOFF 47.0 Monday
185 FOWLER GERALD 35.0 Tuesday
186 FOX GORDON 23.0 Wednesday
187 FOX HADIYA 18.0 Thursday
188 FREEMAN HALEY 28.0 Friday
189 FRIEDMAN HANNAH 47.0 Saturday
190 FRIEDMAN HANNAH 28.0 Sunday
191 FROY HARRY 20.0 Monday
192 GALLANT HEATH 34.0 Tuesday
193 GALLO HEATHER 45.0 Wednesday
194 GARNER HEATHER 24.0 Thursday
195 GATES HEIDI 53.0 Friday
196 GAY HELENA 38.0 Saturday
197 GEIGER HERMAN 60.0 Sunday
198 GERSMEHL IGOR 33.0 Monday
199 GILMORE IGOR 33.0 Tuesday
200 GOBER JACOB 37.0 Wednesday
201 GODWIN JACOB 31.0 Thursday
202 GOLDSWORTHY JACOB 35.0 Friday
203 GOODMAN JACQUE 31.0 Saturday
204 GORDON JADE 41.0 Sunday
205 GRAHAM JAMES 23.0 Monday
206 GRANT JAMES 58.0 Tuesday
207 GRAVES JAMES 60.0 Wednesday
208 GRAY JAMES 54.0 Thursday
209 GRAYSON JAMES 20.0 Friday
210 GREENE JAMES 52.0 Saturday
211 GREY JAMES 36.0 Sunday
212 GRIFFIN JAMIE 48.0 Monday
213 GUGEL JAMYE 47.0 Tuesday
214 GUTHRIE JANE 31.0 Wednesday
215 HACHADORIAN JANE 40.0 Thursday
216 HAGERMAN JANET 59.0 Friday
217 HAIR JANINA 58.0 Saturday
218 HAMBRIDGE JASON 41.0 Sunday
219 HAMM JASON 52.0 Monday
220 HAMMER JASON 31.0 Tuesday
221 HAN JASON 32.0 Wednesday
222 HANG X JEFFREY 52.0 Thursday
223 HANKERSON JENNIFER 36.0 Friday
224 HANSARD JENNIFER 56.0 Saturday
225 HARGETT JENNIFER 24.0 Sunday
226 HARGROVE JENNIFER 57.0 Monday
227 HARRISON JENNIFER 17.0 Tuesday
228 HARTMAN JENNIFER 48.0 Wednesday
229 HARVEY JENNIFER 33.0 Thursday
230 HAWKINS JENNIFER 60.0 Friday
231 HAWVER JENNIFER 31.0 Saturday
232 HAYES JENNIFER 51.0 Sunday
233 HAYNES JENNIFER 30.0 Monday
234 HAYS JENNIFER 30.0 Tuesday
235 HENDERSON JENNIFER 51.0 Wednesday
236 HENDERSON JENNIFER 59.0 Thursday
237 HINTON JENNIFER 28.0 Friday
238 HOBBS JENYAT 24.0 Saturday
239 HOEUNG JEREMY 36.0 Sunday
240 HOFFMAN JEREMY 38.0 Monday
241 HOLLEY JESSICA 20.0 Tuesday
242 HOLMES JESSICA 37.0 Wednesday
243 HOOTEN JESSICA 29.0 Thursday
244 HOROVITZ JESSICA 42.0 Friday
245 HUDALLA JESSICA 17.0 Saturday
246 HUDRON JESUS 51.0 Sunday
247 HUGHES JILL 28.0 Monday
248 HULBERT JODEL 60.0 Tuesday
249 HUMLIE JOE 52.0 Wednesday
250 HUNT JOE 47.0 Thursday
251 INGWELL JOEL 21.0 Friday
252 IVERSON JOEL 43.0 Saturday
253 JACKSON JOEL 53.0 Sunday
254 JACKSON JOELENE 49.0 Monday
255 JACKSON JOHN 40.0 Tuesday
256 JACOBSON JOHN 46.0 Wednesday
257 JENKINS JOHN 19.0 Thursday
258 JENKINS JOHNNY 60.0 Friday
259 JENNY JOHNNY 60.0 Saturday
260 JOHNSON JOHNNY 25.0 Sunday
261 JOHNSON JOHNNY 29.0 Monday
262 JOHNSON JON 45.0 Tuesday
263 JOHNSON JON 27.0 Wednesday
264 JOHNSON JON 18.0 Thursday
265 JONES JONATH 49.0 Friday
266 JONES JORDAN 49.0 Saturday
267 JONES JOSE 53.0 Sunday
268 JOOLE JOSEPH 54.0 Monday
269 JOYNER JOSH 40.0 Tuesday
270 KAN JULIA 37.0 Wednesday
271 KARINS JULIE 22.0 Thursday
272 KEELEY JUSTIN 44.0 Friday
273 KEITH JUSTIN 29.0 Saturday
274 KENNEDY JUSTIN 40.0 Sunday
275 KEOVONGSAK JUSTIN 35.0 Monday
276 KESSLER KARA 27.0 Tuesday
277 KHOSLA KAREN 45.0 Wednesday
278 KIEFER KATHRYN 46.0 Thursday
279 KIM KATHRYN 46.0 Friday
280 KINDER KATHRYN 44.0 Saturday
281 KING KATHRYN 47.0 Sunday
282 KIRVES KATHRYN 50.0 Monday
283 KIRVES KATIE 34.0 Tuesday
284 KNIGHT KELLEY 59.0 Wednesday
285 KOENIG KELLI 25.0 Thursday
286 KORNEGAY KELLY 43.0 Friday
287 LADNER KELLY 17.0 Saturday
288 LAKIN KELLY 57.0 Sunday
289 LAM KENISH 46.0 Monday
290 LANE KENNET 17.0 Tuesday
291 LANIER KENNET 44.0 Wednesday
292 LASSETER KENNET 60.0 Thursday
293 LAWSON KERRY 24.0 Friday
294 LAWSON KEVIN 58.0 Saturday
295 LEACH KEVIN 44.0 Sunday
296 LEONTIS KEVIN 49.0 Monday
297 LEYLAND KEVIN 32.0 Tuesday
298 LEYMANN KIM 22.0 Wednesday
299 LICHTEFELD KIM 32.0 Thursday
300 LIDJI KIMBER 21.0 Friday
301 LONG KIMBER 42.0 Saturday
302 LOWREY KIMBER 44.0 Sunday
303 LUMPKIN KIMBER 44.0 Monday
304 LUMPKIN KRIS 25.0 Tuesday
305 MADDEN KRISTEN 55.0 Wednesday
306 MAIN KRISTI 20.0 Thursday
307 MALONE KRISTI 18.0 Friday
308 MANNINA KRISTI 55.0 Saturday
309 MARKLE KRISTI 58.0 Sunday
310 MARTIN KRISTI 28.0 Monday
311 MATHES KRISTY 35.0 Tuesday
312 MAVRAGANIS KRISTY 35.0 Wednesday
313 MAYFIELD KURT 22.0 Thursday
314 MCCAIN KURT 27.0 Friday
315 MCCARTHY LACEY 34.0 Saturday
316 MCCLENDON LAQUIT 30.0 Sunday
317 MCCORMICK LARISSA 25.0 Monday
318 MCCUSKER LATISH 25.0 Tuesday
319 MCDONALD LAURA 41.0 Wednesday
320 MCDOUGAL LAURA 53.0 Thursday
321 MCGILL LAURA 19.0 Friday
322 MCGOUGH I LAUREN 19.0 Saturday
323 MCGRAW LAUREN 25.0 Sunday
324 MCLAUGHLIN LAUREN 18.0 Monday
325 MCNEAL LAUREN 52.0 Tuesday
326 MCNEILL LAUREN 50.0 Wednesday
327 MCRAE LAUREN 55.0 Thursday
328 MEEKS LAUREN 27.0 Friday
329 MEHRHOF LAUREN 24.0 Saturday
330 MELENDEZ LAUREN 25.0 Sunday
331 MERCIER LAYLA 32.0 Monday
332 MERRITT LEA 26.0 Tuesday
333 MIKA LEAH 54.0 Wednesday
334 MILLER LEAH 57.0 Thursday
335 MILLER LEAH 29.0 Friday
336 MILLS LEANNE 29.0 Saturday
337 MILLS LESLIE 47.0 Sunday
338 MIMBS LESLIE 36.0 Monday
339 MOBLEY LI 41.0 Tuesday
340 MONTROIS LINDSAY 44.0 Wednesday
341 MOORE LINDSAY 37.0 Thursday
342 MOORE LINDSEY 46.0 Friday
343 MORAVEK LINDSEY 31.0 Saturday
344 MORELAND LINDSEY 24.0 Sunday
345 MORRIS LINDSEY 56.0 Monday
346 MORRISON LINSEY 27.0 Tuesday
347 MOSELEY LISA 30.0 Wednesday
348 MULLEN LUANN 38.0 Thursday
349 MURCHISON LYNMAR 31.0 Friday
350 NAPODANO MACON 52.0 Saturday
351 NGUYEN MARC 40.0 Sunday
352 NICHOLSON MARCEL 23.0 Monday
353 NICHOLSON MARCUS 42.0 Tuesday
354 NOLAN MARK 21.0 Wednesday
355 NORMAN MARK 25.0 Thursday
356 NORSE MARK 43.0 Friday
357 NORWOOD MARK 30.0 Saturday
358 NUNGESSER MARK 46.0 Sunday
359 NWAKAMMA MARLON 48.0 Monday
360 OLDEN MARTI 60.0 Tuesday
361 OLMSTEAD MARY 53.0 Wednesday
362 OMEALLY MARY 44.0 Thursday
363 OMERZA MARY 35.0 Friday
364 ORENDORFF MATIAS 42.0 Saturday
365 PACETTI MATIAS 19.0 Sunday
366 PAINE MATIAS 31.0 Monday
367 PATEL MATIAS 34.0 Tuesday
368 PATEL MATT 59.0 Wednesday
369 PATEL MATTHEW 17.0 Thursday
370 PATRICK MATTHEW 57.0 Friday
371 PERKINS MATTHEW 21.0 Saturday
372 PETTIT MATTHEW 46.0 Sunday
373 PHILLIPS MATTHEW 46.0 Monday
374 PIEDMONT MAYA 54.0 Tuesday
375 PILGRIM MEGAN 35.0 Wednesday
376 PIRKLE MEGAN 46.0 Thursday
377 PITTMAN MELANI 33.0 Friday
378 PITTS MELISSA 57.0 Saturday
379 PLUMER MELISSA 36.0 Sunday
380 POHLMAN MELODY 50.0 Monday
381 POOLE MEREDITH 19.0 Tuesday
382 POPHAM MICHAEL 57.0 Wednesday
383 POSENER MICHAEL 18.0 Thursday
384 PRATO MICHAEL 48.0 Friday
385 PRAYTOR MICHAEL 31.0 Saturday
386 PROUD MICHAEL 24.0 Sunday
387 PULLIAM MICHAEL 44.0 Monday
388 PUROHIT MICHAEL 57.0 Tuesday
389 QUINN MICHAEL 42.0 Wednesday
390 RAUCH MICHAEL 33.0 Thursday
391 REAMS MICHAEL 46.0 Friday
392 REDDEN MICHAEL 25.0 Saturday
393 REDMOND MICHAEL 49.0 Sunday
394 REEVES MICHAEL 52.0 Monday
395 REILLEY MICHAEL 37.0 Tuesday
396 REILLY MICHAEL 58.0 Wednesday
397 RENTFROW MICHEL 58.0 Thursday
398 RESPESS MICHEL 29.0 Friday
399 RHODES MICK 21.0 Saturday
400 RICE MIKE 37.0 Sunday
401 RICHARDSON MILES 33.0 Monday
402 RICHARDSON MINDY 57.0 Tuesday
403 RICHARDSON MINKA 21.0 Wednesday
404 RIGDON MYLIEN 18.0 Thursday
405 RIPLEY NANCY 58.0 Friday
406 RIPLEY NATALI 41.0 Saturday
407 RITGER NATALI 19.0 Sunday
408 RIVAS NATE 55.0 Monday
409 RIVERS NATHAN 47.0 Tuesday
410 ROACH NATHAN 35.0 Wednesday
411 ROBINSON NATHAN 33.0 Thursday
412 ROBY NICHOL 43.0 Friday
413 ROGERS NICK 40.0 Saturday
414 ROGERS NICKI 36.0 Sunday
415 ROSEN NICOLA 58.0 Monday
416 ROSTAD NISHIT 24.0 Tuesday
417 RUDOLPH NORAH 35.0 Wednesday
418 RUIZ NOUR 57.0 Thursday
419 RUMSEY ORI 47.0 Friday
420 RUTKOWSKI PAMELA 18.0 Saturday
421 RYAN PANAGI 54.0 Sunday
422 SALMAN PARKER 29.0 Monday
423 SANCHEZ K MBE Y PATRED 45.0 Tuesday
424 SANDERS PAUL 38.0 Wednesday
425 SANDLIN PAUL 46.0 Thursday
426 SATTERWHITE PAULA 40.0 Friday
427 SAWYERS PAYAL 59.0 Saturday
428 SCHAEFFER PEYTON 22.0 Sunday
429 SCHISLER PHILIP 56.0 Monday
430 SCHNURR RACHAEL 26.0 Tuesday
431 SCOTT RACHAEL 46.0 Wednesday
432 SCOTT RACHEL 55.0 Thursday
433 SEAGRAVES RACHEL 35.0 Friday
434 SEARS RACHEL 57.0 Saturday
435 SELF RACHNA 32.0 Sunday
436 SERAFIN RAEGAN 31.0 Monday
437 SETTLE RALECI 47.0 Tuesday
438 SHACKELFORD RANDAL 29.0 Wednesday
439 SHAKIROVA ALEXANDRA RANDY 52.0 Thursday
440 SHANNON RAY 19.0 Friday
441 SHEALY REAGEN 48.0 Saturday
442 SHIELDS REBEKA 28.0 Sunday
443 SHIRLEY REBEKA 57.0 Monday
444 SIBERT REED 54.0 Tuesday
445 SILVEY RENATO 32.0 Wednesday
446 SLAUGHTER RICHARD 60.0 Thursday
447 SMITH RICHARD 32.0 Friday
448 SMITH ROB 27.0 Saturday
449 SMITH ROBBY 41.0 Sunday
450 SMITH ROBERT 44.0 Monday
451 SNELL ROBERT 17.0 Tuesday
452 SPANIER ROBERT 29.0 Wednesday
453 SPIERS ROBERT 33.0 Thursday
454 STARUCH ROBERT 33.0 Friday
455 STEPHENS ROBERT 34.0 Saturday
456 STEWART ROB 57.0 Sunday
457 STEWART ROBERT 52.0 Monday
458 STEWART ROBERTA 26.0 Tuesday
459 STIEBEL ROBERT 17.0 Wednesday
460 STILLWELL ROBERT 58.0 Thursday
461 STOICA ROBIN 44.0 Friday
462 STOLL RONALD 32.0 Saturday
463 STRAHAN RUTABA 52.0 Sunday
464 STREET RYAN 35.0 Monday
465 STRIBLING RYAN 32.0 Tuesday
466 STUART RYAN 41.0 Wednesday
467 SULLIVAN SAM 38.0 Thursday
468 SULLIVAN SARA 23.0 Friday
469 SUNDAL SARAH 48.0 Saturday
470 SWAN SARAH 42.0 Sunday
471 TANG SARAH 37.0 Monday
472 TANNER SARAH 26.0 Tuesday
473 TAYLOR SCOT 59.0 Wednesday
474 TAYLOR SCOTT 19.0 Thursday
475 TAYLOR SCOTT 51.0 Friday
476 TAYLOR CHRIS SCOTT 40.0 Saturday
477 TEEGARDEN SCOTT 55.0 Sunday
478 TERRELL SCOTT 49.0 Monday
479 TERRELL U SCOTT 41.0 Tuesday
480 THOMAS SEAN 32.0 Wednesday
481 THOMAS SETH 28.0 Thursday
482 THOMPSON SETH 17.0 Friday
483 THOMPSON SHALIN 38.0 Saturday
484 THOMPSON SHANNON 50.0 Sunday
485 THORNBERRY SHANTA 60.0 Monday
486 THORNE SHARON 35.0 Tuesday
487 THORNTON SHAWLY 20.0 Wednesday
488 THORNTON SHELBY 25.0 Thursday
489 TINGLE SHELLE 53.0 Friday
490 TITSHAW SHERRI 48.0 Saturday
491 TOBABEN SHONTE 47.0 Sunday
492 TONGE SONG 21.0 Monday
493 TOWLE STELLA 49.0 Tuesday
494 TOWNE STEPHANIE 60.0 Wednesday
495 TRAYLOR STEPHANIE 25.0 Thursday
496 TUCKER STEPHANIE 18.0 Friday
497 TYRRELL STEPHEN 33.0 Saturday
498 TYSON STEVE 42.0 Sunday
499 UPCHURCH STEVEN 53.0 Monday
500 USSERY STEWART 36.0 Tuesday
501 VANDENBARSELAAR SUSAN 26.0 Wednesday
502 VANDERBOOM SUZANNE 55.0 Thursday
503 VARIAN SUZANNE 42.0 Friday
504 VASAN SUZANNE 19.0 Saturday
505 VEXLER SUZANNE 42.0 Sunday
506 VINING TAI 56.0 Monday
507 VOLK TALYA 18.0 Tuesday
508 WADFORD TAMARA 28.0 Wednesday
509 WALTERS TAMMY 28.0 Thursday
510 WANG TANISA 30.0 Friday
511 WANG TANYA 46.0 Saturday
512 WARNES TARA 40.0 Sunday
513 WATKINS TARIEN 42.0 Monday
514 WATTS TERRI 42.0 Tuesday
515 WEATHERFORD THOMAS 20.0 Wednesday
516 WEAVER THOMAS 50.0 Thursday
517 WEBB TIA 57.0 Friday
518 WEDEKIND TIFFANY 49.0 Saturday
519 WEIDEN TIFFANY 56.0 Sunday
520 WELD TIFFANY 38.0 Monday
521 WELLS TIFFANY 55.0 Tuesday
522 WELLS TINA 17.0 Wednesday
523 WEST TINA 26.0 Thursday
524 WHITAKER TINA 60.0 Friday
525 WHITE TODD 30.0 Saturday
526 WHITEHURST TOM 52.0 Sunday
527 WHITFIELD TONY 48.0 Monday
528 WIGGINS TRAVIS 45.0 Tuesday
529 WILDT TRICIA 59.0 Wednesday
530 WILEY TYE 31.0 Thursday
531 WILLIAMS TYLER 20.0 Friday
532 WILLIAMS VANESS 42.0 Saturday
533 WILLIAMS VIKI 31.0 Sunday
534 WILLINGHAM VIMAL 33.0 Monday
535 WILLIS VIVEK 28.0 Tuesday
536 WILLIS WALTER 44.0 Wednesday
537 WILTROUT WALTER 32.0 Thursday
538 WINBORNE WANG 52.0 Friday
539 WINZELER WESLEY 56.0 Saturday
540 WOLFE WILL 44.0 Sunday
541 WOOD WILL 55.0 Monday
542 WOODE WILLIA 54.0 Tuesday
543 WOODER WILLIAM 26.0 Wednesday
544 WOODHAM WILLA 40.0 Thursday
545 WOOLLEY WILLIAM 57.0 Friday
546 WORRALL WILLIAM 28.0 Saturday
547 WRIGHT WILLIAM 60.0 Sunday
548 WYNN WILLIAM 21.0 Monday
549 YEOMANS WILLIAM 42.0 Tuesday
550 YONCHAK WOOKTA 24.0 Wednesday
551 YOUNG XING 60.0 Thursday
552 ZBOINSKA YOUNG 26.0 Friday
553 ZEIGLER ZANE 45.0 Saturday
ACC_Project_WildOutfitters_Data_Model_1.pptx
WildOutfitters.com Course Management System Data Model
*ClassID
ClassDate
ClassStartTime
ClassEndTime
ClassLocation
FKCustID
FKEmpID
FKCourseID
tblClass
tblEmployee
*EmpID
EmpLName
EmpFName
EmpPhNum
EmpEMail
=
One to Many Relationship
tblCustomer
*CustID
CustLName
CustFName
CustAddress
CustCity
CustState
CustZipCode
CustEMail
*CourseID
CourseName
CourseType
tblCourse
=
Unique Identifiers (become the primary keys in your tables)
*
Sample Table View of the Employee Entity
EmpID EmpLName EmpFName
EmpPhNum EmpEMail
1 Johns Sara 555-1213 SJ@wof.com
2 Marks Jennifer 555-1214 JM@wof.com
3 Timberlake Isaac 555-1215 ITL@yourschool.edu
Sample Table View of the Course Entity
CourseID CourseName CourseType
1 Intro. to Day Hiking Half-Day
2 Basic Backpacking Half-Day
3 Rock Climbing Fundamentals All-Day
4 Intro to Kayaking All-Day
Sample Table View of the Customer Entity
CustID CustLName CustFName CustAddress CustCity CustState CustZipCode CustEMail
1 Kregg Pat 123 Main Street Yourtown CA 90200 PK@abc.com
2 Lenten Lisa Rose 12 College Avenue Anytown GA 30600 LRL@school.edu
3 Nicols Estelle 23 Church Street Mytown ND 73040 EN@ecole.edu
Sample Table View of the Class Entity
ClassID ClassDate ClassStartTime
ClassEndTime ClassLocation FKCustID FKEmpID FKCourseID
1 10/28/2006 0900 1200 Rocky Bald
State Park
Conference Room 2 2 2
2 09/16/2006 0900 1100 In-Store 1 2 1
3 08/19/2006 0930 1530 Broad River Pavilion 3 3 4
Access Assignment Readings
Tutorial
1
: Introduction to Microsoft Access
2
00
7
Learning Objectives
After reading this tutorial and completing the associated exercises, you will be able to:
·
Open MS Access.
·
Create tables using MS Access.
·
Create forms and use them to enter data into tables.
Note: Be sure to work through the examples in this tutorial as you read. This is the way you will learn by doing.
Microsoft Access: A Relational Database Application
Microsoft Access is a relational database application that is part of the Microsoft Office suite of applications. A database is an organized way to store data, and database software usually provides features that allow a user to add, delete, update, and manipulate (transform) data. A relational database, such as MS Access, stores data in tables. A table consists of rows (records) and columns (fields). Although the appearance of the records and the fields resembles the rows and columns you may have seen or may study later in a Microsoft Excel worksheet, Access is a much more powerful tool than Excel for organizing, storing, and retrieving data. Much of this additional power comes from the relational model that is implemented by MS Access.
With Excel, you can create data tables that list all data for a particular entity (customer, product, etc.) in a row of the worksheet. So, if a customer makes
10
0 purchases and a business tracks the customer’s address in a database, that address would be listed 100 times. The relational model used by MS Access allows us to store data in separate tables and then link tables via common fields—the primary key/foreign key relationship.
A primary key in a table is a field that contains a unique value for each and every record in the table. In other words, if a field is a primary key, you can’t use a specific value more than once in that column. A foreign key is a field in one table that contains values that are stored in a primary key in another table. If we look at the foreign key for a specific record, we can use the value to look up more data in the table where that field is used as a primary key. We are then guaranteed to find only one record that matches that value. Basically, a foreign key field provides a reference to a record in another table.
For example, suppose we have a Customer table, in which there is a field CustomerID that is a primary key in that table. If a copy of the CustomerID is stored as a foreign key in a CustomerLoan table, then information such as the customer’s address is stored only once, in the Customer table, but can be accessed from any customer loan record. This reduces data redundancy and improves updating and other database functions.
Most of this module will be spent learning by building. First you will create a database consisting of one table, and then you will create a form to enter data. In later modules, you will learn how to query this table for information and to create reports based on this information. You will also learn how to add tables to this database; then you will create the remaining tables; and finally you will link the tables by creating relationships and to query a multi-table database.
Starting a New Access Database
Let’s start by opening a new Access database and taking a quick look at the MS Access interface.
1.
Open MS Access by clicking its icon on the desktop or selecting it from a Windows menu.
You should now see the “Getting Started with Microsoft Office Access” screen shown in Access Figure
1-1
.
Access Figure 1-1
Opening MS Access.
With this screen you have several options. You can work on a database that you created earlier by selecting it from a list of Recent Databases on the right side of the screen, or you can start a new database. If you choose to start a new database, it can be a blank one for which you will build all components from scratch, or you can choose from a library of prebuilt templates. Microsoft has supplied a large library of templates of commonly used database applications. For our purposes, we’ll start a new blank database.
2.
Click on the Blank Database icon.
A window will appear on the right side of the screen that will prompt you to enter a file name for your database.
We’ll enter Example_Database for now.
Note that Access 2007 uses.accdb as the file extension for Access database files, as shown in Access Figure
1-2
.
Access Figure 1-2
Starting a new blank database.
After entering the file name, click on the Create button.
Note that in Access Figure 1-2 you can see the location where the new database will be stored just below the text box where you enter the new database name. To change this, click on the folder to the right of the text box, then navigate to the location where you want to store the file.
The MS Access Interface
You should now see the main Access interface that you will be working with as you create or modify an Access database. The basic structure of the Access user interface is shown in Access Figure
1-
3
, and its components are listed in Access Table 1-1.
|
Access Figure 1-3 Starting a new blank database. |
Access Table 1-1
Components of the Access user interface
Component
Description
Context tab
Tabs above the ribbon that allow you display different commands based on the category that you choose.
Status bar
Part of the window that provides information about the progress of any ongoing process.
Navigation pane
The area that displays the objects included in your current database. You can use this to navigate to the object that you want to work on.
Ribbon
A strip across the upper part of the window that contains commands that reflect the active context tab and the object that you are currently working on.
Object window
The area of the window that displays any open database objects.
In order to understand the names of some of these components, you need to understand that an Access database is generally a collection of objects. These objects could be tables, forms, queries, reports, and macros. In this tutorial, we will work with two of these types of Access objects: tables and forms. You’ll see other object types in later tutorials. As indicated in Access Figure 1-3 and Access Table 1-1, the object window will display the object you are currently working on. The look of this window will change depending on what the object is. To select an object to work with, we usually find it in the navigation pane.
Access Ribbons
In Access Figure 1-3, there are four main Access ribbon context tabs displayed, which make it easy to find the commands that you need. (You can actually customize Access and add one or two more if needed.) In addition, other ribbons will become available as you work with various objects. For example, the context tab for the Datasheet ribbon is shown in Access Figure 1-3 because we started a new Access database and the software assumes that we are currently working with a new table.
The next several figures will provide a quick overview of the primary Access ribbons. You will get very familiar with these as you work through these tutorials, so there is no need to memorize them now. Just take a quick look now and then refer back to these pages as needed.
Notice that each ribbon has some things in common with all the others. Commands are arranged in a series of groups. Each group includes a set of commands that let you do related tasks. Some controls are accompanied by downward-pointing arrows. When these are clicked, a menu or palette with more related options is displayed. You should also notice that each group has a small arrow in the lower right corner. By clicking this arrow you can open a dialog box for the group that provides even more options that you can use to complete your work.
The Home ribbon, shown in Access Figure
1-
4
, contains the most commonly used Access commands. The Views group provides commands that let you change how a particular object is displayed. Commands such as Copy, Cut, and Paste are located here in the Clipboard group. Formatting commands are provided in the Font and Rich Text groups. The Records group provides commands for working with the records (rows) of an Access data table. The Sort & Filter group commands can be used to display only items that fit a given criteria. Finally, the Find group provides tools for searching for items in your database.
|
Access Figure 1-4 The Home ribbon. |
The Create ribbon, shown in Access Figure
1-
5
, includes commands that are used to create various Access objects. The Tables group provides commands for creating tables, the Forms group provides commands for creating forms, and the Reports group provides commands for creating Reports. The Other group provides commands for other types of objects such as queries and macros.
|
Access Figure 1-5 The Create ribbon. |
The primary purpose of Access is to work with data. The External Data ribbon, shown in Access Figure
1-
6
, provides commands that let you do more with your data. The Import group provides commands for bringing data in to your database from external sources. The Export group provides commands that allow you to use the data in your current database in other applications. The Collect Data group provides commands that let you collaborate to communicate or collect data. The SharePoint Lists group provides commands that allow you to collaborate using Microsoft Windows SharePoint services.
|
Access Figure 1-6 The External Data ribbon. |
The Database Tools ribbon, shown in Access Figure
1-7
, provides commands that let you work with your database is various ways. The Macro group has commands that let you work with macros, which are saved sets of instructions for automating a task in Access. The Show/Hide group has commands for working with relationships between objects in your database. The Analyze group has tools for checking your database and improving its performance. The Move Data group commands let you make connections to a server to send or retrieve data. The Database Tools group provides miscellaneous commands that do not fit well in the other groups.
|
Access Figure 1-7 The Database Tools ribbon. |
Building an Access Table
For this unit on MS Access, we will use a database example involving an Internet-based bank. We will start with a table that has information on the types of loans that the bank makes: home, auto, student, and so on. This information will include an ID number for each type of loan, the name of the loan type, the interest rate on this type of loan, and the minimum amount on this type of loan that the bank will extend. Later on we will add tables on the bank’s customers and the loans made to customers.
1.
Start by opening Access and creating a new database.
Give the name: Bank_Database_Example.accdb to the database.
You should be looking at a screen like that shown in Access Figure 1-
8
.
Access Figure 1-8
The new Bank Database Example database.
A new database table is shown in the Access Object window.
Notice that the navigator pane shows only one object: the current table.
A new Access database table is now shown in its Datasheet view. We’ll see it using another view, the Design view, a little later. Notice that the current context ribbon matches what you are seeing, namely, the datasheet ribbon.
Although the datasheet view of a table resembles the grid that you may have seen if you have used Excel, it does not work the in the same way. When the table has been completely created, you will be able to enter data in rows and columns, but the cells (intersection of rows and columns) usually do not refer to each other as they might in Excel.
Let’s continue and see how we can create a table that fits our requirements. To create the table, we need to define the fields. Remember that a table represents some “things” that we want to keep data about. In this case, the things are types of loans that a bank wants to offer. The fields are the categories of data that we want to remember for each thing.
Usually, field names are created by combining the name of the entity that corresponds to the table and a logical name for the attribute that will be stored in the field. Since this table will store data about loans, all of our field names will start with the word Loan. We then come up with a name for the particular field. Note that it is often acceptable to abbreviate the first part of the name. For example, CustID might be an acceptable name for the primary key in a Customer table. Also, it is customary to capitalize the first letter of each word in the field name. You will do this later in this module when you create the fields LoanType, LoanRate, and LoanMin. The goal is to keep the names easily identifiable, understood, and not susceptible to being mixed up.
For this example, we would like to store a loan ID, a loan type, a loan rate, and a minimum loan amount for each type of loan. These will be our fields. There are several ways to add fields to our table. We’ll show you how to add fields in several of these ways.
2.
Our first method for entering a new field will be right on the datasheet.
Double-click on the tab that reads Add New Field.
Type the field name LoanType as shown in Access Figure 1-
9
.
Access Figure 1-9
Adding the LoanType field.
That’s almost it! You’ve actually added a new field.
Fields have several settings that we may want to change. The settings for this field are the defaults.
When you create a field for your database table, you need to set several field properties. It turns out that there are a lot of field properties you could set, but only a few are essential. Access Table 1-2 lists the most commonly set field properties. Some of these we can set using commands on the Datasheet ribbon.
Access Table 1-2
Commonly set field properties
Data Type
Used to define the type of data that is to be stored in the field. Some Access data types include Text, Number, Date/Time, Currency, and AutoNumber. This is probably the most important field property that you need to set. If not changed, the field is Text data type by default.
Format
Determines how the data is displayed. Examples are percent and currency formats. The format does not affect the actual data that is stored in the field, only how it looks.
Unique
Sets whether a value in a field must be unique. When this property is set, Access will not allow you to add a new record if the entered value has been used in another record.
Is Required
Sets whether a value is required in this field. When this property is set, Access will not allow you to add a new record unless a value is entered for this field.
Field size
Technically, this determines the amount of memory needed to store a data value in the field. With Text fields it can be set by specifying the number of characters that can be used in the field (maximum 255). With numeric data types, the size is determined by specifying the type of number to be stored in the field (Long Integer, Double, etc.).
Input mask
With this property you can specify a pattern for all data values that you want to enter into the field. It’s a good way to help make sure that all data will be entered with correct values.
Default value
A value that will be included in the field unless changed by the user entering the data.
3.
Some of the field properties can be set using the commands in the Data Type & Formatting group on the Datasheet ribbon.
While the LoanType field is selected, click the arrow for the Data Type list to see the possible data types you can select.
Select the Text data type for this field as shown in Access Figure 1-10.
Access Figure 1-10
Setting the LoanType data type.
Note the other field properties that you can set using the commands on this group.
4.
Let’s try another way to add a new field.
Click on the New Field command in the Fields & Columns group on the Datasheet ribbon, as shown in Access Figure 1-
11
.
Access Figure 1-11
Using the New Field command.
5.
You should now see the Field Template list appear on the right side of the Access workspace.
This is a list of predefined fields that you can choose from. Most of the commonly used field types are there.
You can choose one that should be close to what you want and then adjust field properties to tailor the field to your needs.
Select a Number field from the list as shown in Access Figure 1-12.
Access Figure 1-12
Selecting the Number field template.
6.
A new field (column) with the Number data type has now been added to the table.
Notice that the field name has also been filled in with the rather unimaginative name Number as shown in Access Figure 1-13.
Access Figure 1-13
The new Number field.
Let’s change that quickly.
7.
To rename the field, double-click on the field name in the table.
Then type the field name LoanRate as shown in Access Figure 1-14.
Access Figure 1-14
Naming the LoanRate field.
8.
Interest rates on loans are generally expressed as percentages.
Notice the quick formatting icons available in the Data Type and Formatting group.
Click the % icon to format this field to percent.
You should see that the Format box now displays the word Percent as in Access Figure 1-15.
Access Figure 1-15
Formatting the field to Percent.
We’ve been working in the Datasheet view. We’ll find that we have far more options if we work in Design view.
9.
To get to there, select Design View from the View options on the Datasheet ribbon as shown in Access Figure 1-16.
Access Figure 1-16
Changing to Design view.
Before switching to Design view, Access will prompt you to save your new table.
10.
Type the name loans into the text box and click OK as shown in Access Figure 1-17.
Access Figure 1-17
Saving the table.
Important: Saving in Access is unlike saving in other Office software such as Word or Excel. Access saves the components that you create and the data that you enter as you work. You have to click the Save icon only on special, rare occasions.
11.
The object window should now change to display a listing of the fields in your table as shown in Access Figure 1-18.
Access Figure 1-18
loans Table in Design view.
Notice that the names and data types of the fields that that we have entered are already listed in the upper table.
We’ll use the Design view to add another field and change some current field properties.
12.
To add a new field in the Design View, simply type a new field name in the Field Name list in the upper part of the Object window.
Add the field name LoanMin to the list as shown in Access Figure 1-19.
Access Figure 1-19
Adding LoanMin field in Design view.
When you do, the Data Type will automatically show up as Text. The lower part of the window will display a list of field settings and values.
13.
A minimum loan amount is a number that represents currency.
Click the arrow on the data type box for this field and select the Currency data type from the list as shown in Access Figure 1-20.
Access Figure 1-20
Setting LoanMin data type in Design view.
You should notice that the field setting list will change to present options available for fields of this data type.
Let’s take a look at the fields in our table and make any adjustments needed to complete the table. First, have a look at the first field name, ID. Recall that this field was automatically included when we created a new table. This is the primary key for the table. Remember, a primary key is a field in a table that will contain a unique value for each and every record in the table. It’s important that all tables in your databases include a field that can serve as the primary key. So, Access simply assumes that you want a field that can do this and includes the ID field by default. You can tell that this is the primary key field because there is a key icon displayed to the right of the field name in the list.
Access also assumes that you want this field to use the AutoNumber data type. AutoNumber is a special data type provided by the Access software to use with primary keys. Access will automatically fill a field using the AutoNumber data type with the next unique value. Access makes sure that an AutoNumber field is unique by never assigning a value to the field again, even after a record is deleted. The default field size for an AutoNumber field is Long Integer. The field size determines how much memory will be used to store numeric data in each field. For numbers, the amount of memory and format is set when you choose a numeric data type for the field type. You need to be aware of this. When a primary key is used as a foreign key in another table, you need to make sure that the foreign key uses a data type that is compatible with the data type of the primary key (Long Integer for AutoNumber).
Thinking Critically:
Would you use the AutoNumber data type if the bank has its own loan identification system? Why or why not?
For our needs, the AutoNumber data type will work well, but it would be nice to have a more descriptive field name.
Access 2007 Animated Tutorials: Field Sizes Numbers
Access 2007 Animated Tutorials: Field Sizes Text
14.
Click the field name for ID and change the name to LoanID as shown in Access Figure 1-21.
Access Figure 1-21
Adjusting LoanID field name.
Have a look at the field properties available for this field and their current settings. We’ll leave the rest of these settings as is.
15.
Let’s also add a description for this field.
Type A unique ID for a Loan Type in the Description column for the LoanID field as shown in Access Figure 1-22.
Access Figure 1-22
Adding a field description for LoanID.
Descriptions can be a very important part of documenting your database. They can help others working with your database recognize the purpose of a field.
Access 2007 Animated Tutorials: Properties
16.
After entering the description, you’ll probably see an icon that looks like a lightning bolt with an arrow next to it.
This provides a list of Property Update Options.
Whenever you see this icon, you should click the arrow (don’t ignore it!) to see available options, as shown in Access Figure 1-23, and select the one most appropriate for your use.
Access Figure 1-23
Property update options.
For now, select the option that starts: “Update Status Bar…”
17.
You’ll probably get the same message that we got, as shown in Access Figure 1-24: “No objects needed to update.”
Access Figure 1-24
Message from Property Update.
This is because we have no other objects in our database besides this table.
If there were other objects, it would be a good idea to update the property as we did in Step 16. So, it’s good to start getting into the habit now.
18.
Now go ahead and add field descriptions for the rest of the fields as shown in Access Figure 1-25.
Access Figure 1-25
Field descriptions.
19.
The default field width for a text field is 255 characters. This is longer than we will need for our LoanType field.
Select the LoanType field.
Now, adjust the Field Size in the field property list to 100 (Access Figure 1-26).
Access Figure 1-26
Setting field size for LoanType.
This means we can enter any text into this field that is 100 characters or less.
20.
Now have a look at the LoanRate data type.
The default field size for a Number data type is Long Integer. This means that we can store only integer values (values with no fractional part) in the field.
Of course, percents are all about fractions.
Click the arrow for the field size box and select Double, as shown in Access Figure 1-27.
Access Figure 1-27
Setting Field Size for LoanRate.
Double will allow us to store values with decimal portions. The difference between Double and Single is that the values stored in a Double field can be more precise.
Thinking Critically:
Are there any other field properties that you think should be changed? If so, what? Go ahead and make those changes before moving on.
At this point, we are finished with designing the structure for our table. Next, we will take a look at how we can enter data into the table. In general, we could enter data using the Datasheet view, or, even better, we can create a form object to use for data entry. If you are creating a database with more than one table, you should wait and enter data only after all tables and their relationships are created. We’ll add more tables to this example database in a later tutorial.
Using an Access Datasheet for Data Entry
With the table in the Datasheet view, we can simply type a data value into its appropriate field (column). To do this, we need to return to the Datasheet view.
|
1. Click the View command on the Design ribbon.
Access Figure 1-28 Selecting DataSheet view. We’ll select Datasheet view. Go ahead and select that now. 2. Access requires that you save significant changes to the database as you go along.
Access Figure 1-29 Saving table changes. Click Yes to save the table and move to the DataSheet view. 3. Now you’ll see the table in the Datasheet view once again. This time, however, all of the fields that you’ve added to the table are displayed as columns as shown in Access Figure 1-30.
Access Figure 1-30 The Datasheet view with all table fields. Notice the bar at the bottom of the Datasheet that shows which record you are currently highlighting (1 of 1). Later, you can use these controls here to browse through the table if desired. 4. To enter data using the Datasheet view, you simply click in the field where you want to type data and type it.
Access Figure 1-31 Entering LoanType data. Notice that the number 1 appeared automatically in the LoanID field. Why? 5. As you enter data into a field, the (New) record moves to the line below the one in which you are typing.
Access Figure 1-32 Entering database records. In the next section, we’ll see another way to enter data into a database table. |
Using an Access Form for Data Entry
You will now learn how to create an Access form to use in data entry. Forms are used to provide a more formal and sometimes easier method of data entry than simply using the Datasheet view of a table. They are also important when you want users to enter data but you don’t want them to have access to the actual table. To enter more data into the loans table, you will create the loans form. Let’s begin.
1.
While viewing the table, click on the Form command.
You’ll find it in the Forms group of the Create ribbon as shown in Access Figure 1-33.
Access Figure 1-33
Creating a form
2.
You should now see a form for the loans table as shown in Access Figure 1-34.
Access Figure 1-34
The loans form.
Notice that this form contains a label and text box for each of the fields in our database.
These text boxes are provided for data entry.
This form is bound to our table. When a value is entered into a text box, the value will be entered into the corresponding field of the table.
As you can see in Access Figure 1-34, the binding between the form and the table works both ways. The data in the currently selected record, record 1, is displayed in the form. You can move through the records using the browsing buttons () at the bottom of the form. The and arrows will move one record at a time, and the and arrows will move to the first record in the table and the last, respectively. The number indicates the number of the record that is currently displayed.
The form is functional as is, but it’s not very pretty. Let’s first make the form look a little better. Then we’ll use it to add a new record or two. The new form is currently in Layout view. While in Layout view, you’ll notice that two form-related ribbons are available: the Format and Arrange ribbons under Form Layout Tools. These ribbons include commands that let us change how the format looks and how its elements are arranged.
3.
On the Format menu you’ll find the AutoFormat group. Click the More arrow to get a menu of format choices.
Pick one that you like and see how the look of the form changes.
We chose one called “Trek” as shown in Access Figure 1-35.
Access Figure 1-35
Selecting a form AutoFormat.
4.
We can also easily adjust the labels on the form.
Notice that the labels used are derived from the table name and the field names.
Because it will be users (not you the designer) who are likely to enter data, we want to make these labels clearer for them.
Click on the Form title. Replace loans with the form title Loan Type Entry Form as shown in Access Figure 1-36.
Access Figure 1-36
Editing the form title.
5.
You can edit the field labels in the same way.
Click the label LoanID.
Edit it by adding a space to make Loan ID as shown in Access Figure 1-37.
Access Figure 1-37
Editing the field label LoanID.
6.
Edit the rest of the field labels to be more user-friendly as shown in Access Figure 1-38.
Access Figure 1-38
Editing field labels.
7.
Let’s make sure that our changes have actually been made to the form.
Click the Save icon on the Quick Access Toolbar at the top left of the Access window.
Save the form with the name loans (Access Figure 1-39).
Access Figure 1-39
Saving the loans form.
You’ll notice that this is the same name as the table. Won’t it cause a problem to name two components with the same name?
8.
After saving, you’ll notice a change in the navigation pane.
You should see that the components in your database, the loans table and the loans form, are listed in separate areas by object type.
If you don’t see this, click the arrow at the top of the navigation pane and select All Access Objects as shown in Access Figure 1-40.
Access Figure 1-40
Database components in navigation pane.
Now that our form looks the way we want and we have it saved, let’s use it to add some new data records.
9.
Make sure that the form is in Form view so that you can add data.
Do this by selecting Form View from the View menu from the Format ribbon as shown in Access Figure 1-41.
Access Figure 1-41
Changing to Form view.
10.
Now click on the New Record button, , which is part of the form navigation button at the bottom of the Object window, as shown in Access Figure 1-42.
Access Figure 1-42
The New Record navigation button.
11.
You’ll see a new, blank record on the form as shown in Access Figure 1-43.
Access Figure 1-43
A new blank record in the form.
Notice that the LoanID field gives you a big clue that this is a new record.
12.
To enter data, simply type data into the appropriate text box.
For this example, enter Auto Loan 36-Month for the loan type, 8.5 for the loan rate, and 5000 for the loan minimum as shown in Access Figure 1-44.
Access Figure 1-44
Adding a new record.
There is no need to type the % or $ symbols for the loan rate and minimum. They are automatically added because of the field data types and formats.
Using your form, add data until you have all six of the records shown in Access Table 1-3. If you have been following along, then the first three records should already be entered.
Access Table 1-3
loans data
Loan ID
Loan Type
Loan Rate
Loan Minimum
1
15 Year Mortgage
4.50%
$50,000.00
2
30 Year Mortgage
5.50%
$75,000.00
3
Auto Loan 36-Month
8.50%
$5,000.00
4
Auto Loan 48-Month
9.50%
$7,500.00
5
Auto Loan 60-Month
10.50%
$10,000.00
6
Student
5.00%
$2,000.00
13.
When completed, you should see the last record in the form as shown in Access Figure 1-45.
Access Figure 1-45
Last data record for loans.
You can use the navigation buttons to browse the records and make any necessary corrections.
Let’s check to see what the new records look like in the Datasheet view of the table.
14.
Click on the loans table in the Navigation pane.
Select the Datasheet view from the View options.
What’s this? Only the first two records that we entered directly into the Datasheet are listed as shown in Access Figure 1-46. What the heck is going on?
Access Figure 1-46
The loans table Datasheet view.
15.
It turns out that there’s nothing wrong. The view just needs to be refreshed.
To do this, go to the Home ribbon and click Refresh All as shown in Access Figure 1-47.
Access Figure 1-47
Refreshing the Datasheet view.
16.
After refreshing the Datasheet view, you should now see all six of the records that you added as in Access Figure 1-48.
Access Figure 1-48
The refreshed DataSheet view.
Whew! Nothing lost!
Now, you’ve got a good start at building an Access database. You can build a table for storing data, and you can build a related form to use for entering the data. In later tutorials we’ll add to your skills so that you can include multiple tables, ask questions about the data using queries, and make reports based on the data in the database.
|
Exercises to Build Your Knowledge of Access Match each of the Access terms with its description. 1. _________ Datasheet view 2. _________ Design view 3. _________ Field 4. _________ Foreign key 5. _________ Form 6. _________ Object window 7. _________ Primary key 8. _________ Record 9. _________ Relational database 10. _________ Table |
|
Access Tutorial 1: Mini-Case 1
Scenario: Your Task: Access Table 1-4 Fields for Mini-case 1 database Field Description ID Number A 6-digit identification chosen by Nick that may use both numbers and letters. Dealer Cost The price that Nick paid for the car. Should be stored in the appropriate currency format. Blue Book The estimated used car sales price based on the industry standard “Blue Book” estimates. Should be stored in the appropriate currency format. Type The type of vehicle. Data values here can include car, truck, SUV, etc. Sold A Yes/No field that can be checked as Yes for vehicles that have been sold. Create a form for entering data into the vehicles table. Use your form to enter the data listed in Access Table 1-5: Access Table 1-5 Data for Mini-case 1 database ID Cost Blue Book Type Sold 01086 $13,550 $17,888 Car Yes 01145 $10,500 $12,599 Truck No 01319 $8,100 $9,300 SUV No A0554 $11,430 $12,800 Car Yes |
|
Access Tutorial 1: Mini-Case 2
Scenario: Your Task: Access Table 1-6 Fields for Mini-Case 2 database Field Description photo_ID A photo ID number that can be automatically generated. photographer The name of the family member who took the photo. date The date when the photo was taken. location The location where the photo was taken. subject A brief description of the photo’s subject matter. color A Yes/No field that can be checked as Yes for color photos and No for black/white photos. filename The file name of the photo stored on Amy’s hard drive. Create a form for entering data into the vehicles table. Use your form to enter data. Enter about 10 records. An example record is provided in Access Table 1-7. Access Table 1-7 Example data for Mini-Case 2 database Field Data photo_ID 1 photographer Julie date 8/7/2007 location Burgundy, France subject The Roche de Solutre. A natural and prehistoric site in France. color Yes filename roche001 |
Tutorial 2: Queries and Reports in Microsoft Access 2007
|
After reading this tutorial and completing the associated exercises, you will be able to: · Create simple queries in MS Access. · Work with more complex queries. · Create reports from tables and queries. Files for Download: You must download the following files associated with this tutorial: · The tutorial example template file: ACC_2_Bank_Database_Example.accdb · The Mini-Case template file: ACC_2_MC_Employees.accdb Starter File: Tutorial 2: Bank Database Example Starter File: Tutorial 2: Mini-Case Employees |
Creating Simple Queries
A query is the primary tool that you use in Access to get answers to your questions about the data. Queries are used to view, change, and analyze data in different ways. Queries allow you to specify what data you want to see. Usually this view is a subset of the available data that you created by selecting fields from tables or other queries. You can also use them as the source for forms, reports, and data access pages.
There are several types of queries to choose from, depending on the records that you want to retrieve from the database. In this tutorial we will limit our discussion to the most basic type of query: the select query. A select query will basically look through one or more tables and select and display records that match the parameters of the query. With a select query you can specify the table or tables from which you want to retrieve data, specify the fields from those tables that you want to display, and filter the records shown by some criteria. We will see that even while working only with select queries, there are several different variations to choose from and methods for creating them.
To begin your work in this tutorial, you should carry out the following operations:
|
· Download the database file that accompanies this lab tutorial (ACC_2_Bank_Database_Example.accdb). Save it to secondary storage. · Launch the MS Access Program. · Choose Open an Existing file and open the ACC_2_Bank_Database_Example.accdb file. |
A Simple Detail Query
If all we want is a simple select query—that is, one that pulls data from one or more fields and displays it without filtering—we can create one easily using the Query Wizard. Let’s see how we can use the Query Wizard to create a query that will display only the Loan Type, Loan Rate, and Loan Minimum fields from our loans table.
1.
After loading the database, the loans table in Datasheet view should be displayed. If not, you can select it in the navigation pane.
Notice in the navigation pane that there are currently two components in the database: the loans table and the loans form.
Select the Create ribbon.
Now, click on the Query Wizard command in the Others group as shown in Access Figure
2-1
.
Access Figure 2-1
Starting the Query Wizard.
2.
A New Query dialog will appear with a list of several types of Access queries that you can create.
Select the first one on the list, Simple Query Wizard, and click OK as shown in Access Figure 2-2.
Access Figure 2-2
The New Query dialog.
The Simple Query Wizard consists of several pages. On each page you select the options that you want and then click Next to advance to the next page.
3.
On the first page of the Query Wizard you select the tables and fields that you want to be a part of the database.
We currently have only one table, but let’s see how we would select a table anyway.
Click the arrow on the list box labeled Tables/Queries, as shown in Access Figure 2-3.
Access Figure 2-3
The Simple Query Wizard.
4.
After clicking the arrow, you’ll see a listing of the tables and queries that exist in the database.
Currently, we have only one table, so the list is not long.
Highlight the item from the list that includes the data that you want to select, as shown in Access Figure 2-4.
Access Figure 2-4
Choosing tables for a query.
5.
After you have selected a table (or a query), a list of the Available Fields in that table will be displayed.
To choose a field, highlight it and click the arrow to add it to the Selected Fields list as shown in Access Figure 2-5.
Access Figure 2-5
Selecting a field.
The right-facing arrows will add a field to the Selected Fields list, whereas the left-facing arrows will remove it. The double arrows can be used to add or remove all fields in the lists.
6.
After you click the right-facing arrow, the selected field will be moved from the Available Fields list to the Selected Fields list as shown in Access Figure 2-6.
Access Figure 2-6
LoanType in the Selected Fields list.
7.
Go ahead and select the LoanRate and LoanMin fields for the query.
Your list should resemble Access Figure 2-7.
Access Figure 2-7
The selected fields.
When all of the fields that you want are displayed in the Selected Fields list, click the Next button to move to the next page of the Query Wizard.
8.
On the second page of the Simple Query Wizard you can choose between two types of simple queries.
For this one, choose Detail query, as shown in Access Figure 2-8, and click Next to go to the next page of the wizard.
Access Figure 2-8
Selecting a Detail query type.
Note that this type of query will display all of the records from the selected fields.
We’ll take a look at the Summary query type in the next section.
9.
On the final page of the Wizard you can name the query and choose to either open the query or modify its design.
Type the query title All_Loan_Types into the text box provided.
Select the option that lets you open the query and click the Finish button as shown in Access Figure 2-9.
Access Figure 2-9
Naming the query.
10.
You should now see the query results displayed in the Object window as shown in Access Figure 2-10.
Access Figure 2-10
The query results.
Notice that this looks almost like the Datasheet view of the loans table. There are two major differences, however:
The LoanID field is not displayed. Remember that we didn’t choose to add this one to the query.
We cannot add data on this view.
11.
You can also see that a new section for queries and the new query have been added to the list in the Navigation Pane.
You can adjust this view somewhat.
Either right-click on the field heading or click the arrow next to the LoanRate field heading.
Select Sort Smallest to Largest to sort the data by increasing loan rate as shown in Access Figure 2-11.
Access Figure 2-11
Sorting the query results.
12.
You can see the sorted query results in Access Figure 2-12.
Access Figure 2-12
The sorted query results.
|
Thinking Critically: A query provides the answer to a question. Assume that the query in this first example is the answer. What is the question? |
A Simple Summary Query
While making the previous query we saw that we can use the Simple Query Wizard to make two types of queries: a Detail query, which shows all of the data from the selected fields, or a Summary query. As the name implies, a summary query is used to obtain summary values such as totals and averages from the data in the database. Let’s see how we can quickly create a summary query to answer the questions: What is the average loan rate? What are the minimum and maximum values in the loan minimum field? How many records are in the table?
|
1. As before, click the Query Wizard command on the Create ribbon to start the Query Wizard.
Access Figure 2-13 Starting the Query Wizard. 2. For this query, select the LoanRate and LoanMin fields as shown in Access Figure 2-14.
Access Figure 2-14 Selecting the LoanRate and LoanMin fields. Click Next. 3. This time, select the Summary query type.
Access Figure 2-15 Selecting a summary query. 4. Notice that you have several types of summary values that you can obtain for each field.
Access Figure 2-16 Choosing summary options. 5. As before, you should name your query. Use the name Summary_loans_Query.
Access Figure 2-17 Naming the summary query. 6. Your query results should look like those shown in Access Figure 2-18.
Access Figure 2-18 The summary query results. Notice that for this query Access has summarized the values into a single set of numbers. The query field headings indicate what each summary value represents. |
Using Query Design
As you might expect from the name, the Simple Query Wizard is mostly useful when we have relatively simple questions. As our questions become more complex, we need more options for creating more complex queries. Let’s take a look at how we can use the Query Design tool to make more complex queries.
In this example we’ll answer the question: Which loan types and rates have a minimum loan value that is greater than $10,000? To answer this question, we will want to see the LoanType, LoanRate, and LoanMin fields. But we will want to see only those records for which the LoanMin field is over 10,000.
1.
This time we’ll start to build the query by selecting the Query Design command on the Create ribbon as shown in Access Figure 2-19.
Access Figure 2-19
Selecting Query Design.
2.
The Query Design view will be displayed in the Object window.
First, you’ll need to select the source data for the query. This can be from tables or other queries in your database.
For now, select the loans table and click Add, as shown in Access Figure 2-20.
Access Figure 2-20
Selecting the query source.
Then click the Close button.
3.
You should now see the Query Design view, shown in Access Figure 2-21.
Access Figure 2-21
The Query Design view.
The Object window is divided into two parts.
In the top part you will see boxes representing the tables or queries that will be used as the source of your query. Each box is a window that you can move or resize as needed.
On the bottom is a form, which you fill out to specify what the query will show.
The query design form uses a method known as query by example (QBE). This basically means that the query is created by showing Access an example of what the query results should contain. This is in contrast to the more standard form of query, based on Structured Query Language (SQL). SQL will be covered in another unit within the Access tutorial.
The primary parts of the QBE form are shown in Access Figure 2-21. These parts are:
·
Field row (A): Adds a field to the result of the active query
·
Table row (B): Indicates the table in which the field is located
·
Sort row (C): Used to indicate the type of sort for the field in the active query; can be ascending or descending
·
Show check box (D): Determines whether or not the field is displayed in the active query
·
Table(s) area (E): Displays the tables from which the fields and records of the query will be taken
·
Criteria rows (F): Used to define one or more limiting conditions that are used to filter the records shown
We’ll use this form to create our query. The first step is to select the fields that we need for the query. There are three ways you can do this.
4.
Click and drag the LoanType field from the loans table window to the first column of the Field row in the form.
This will add the LoanType field to the query. Notice that the Table row is automatically set.
You can also click the arrow on a field row cell to choose the field from a list. Use this method to add the LoanRate field to the query, as shown in Access Figure 2-22.
Access Figure 2-22
Adding a field to the query.
5.
Select the LoanMin field for the third column on the form as shown in Access Figure 2-23.
Access Figure 2-23
Adding LoanMin field.
Notice that the Show check box is checked by default for each of these fields.
Our next step is to adjust the settings for the fields. For this we might add filtering criteria, choose not to display some of the fields, or sort the results based on one of the fields. Let’s add a filtering criterion. We’ll show you an example of one now and discuss criteria in more detail later in the tutorial.
6.
In the QBE form, click in the first cell of the Criterion rows for the LoanMin field.
Type: > 10000 as shown in Access Figure 2-24.
Access Figure 2-24
Add a query criterion.
With this criterion we are telling Access to show us only those records with a minimum loan amount greater than $10,000.
7.
Click the Sort cell for the LoanRate field on the QBE form.
When the arrow appears, click on it to see the list of sorting options.
Select Ascending, as shown in Access Figure 2-25, to sort the records by increasing LoanRate.
Access Figure 2-25
Sorting by LoanRate.
8.
Your final query design should resemble Access Figure 2-26.
Access Figure 2-26
Final query design.
Let’s save the design by clicking on the Save icon on the Quick Access Toolbar.
9.
The Save As dialog will appear.
Give your query the name Loan_Min_>10K and click OK as shown in Access Figure 2-27.
Access Figure 2-27
Saving the Loan_Min_>10K query.
10.
As with Access tables and forms, there are several views available with queries.
Let’s check the results of our query by selecting the Datasheet view.
Click the View command and select Datasheet View as shown in Access Figure 2-28.
Access Figure 2-28
Switching to Datasheet view.
Your query results will appear like those shown in Access Figure 2-29.
Access Figure 2-29
The Loan_Min_>10K query results.
|
Thinking Critically: Are these query results correct? How can you check? It’s important always to test your queries to see that they are formulated correctly. |
Query Criteria Explained
In our most recent example we included a query criterion that was used to filter the displayed data and limit the records to only those that met the query condition. The criterion that we added to the query let us filter the results to answer the question: “What loan types have a loan minimum greater than $10,000?”
The criteria that are used to filter query records are entered into the criteria rows using conditions. These conditions are similar to those that you may encounter with IF functions in MS Excel but without the cell references. A typical query criterion will use a relational operator (such as =, <, >) and a value. Each record will be evaluated against the criterion, and if there is a match, then the record will be included in the query results. Access Table 2-1 lists other relational operators that may be used. Note the special characters that are used to surround text and date values in the conditions. These are known as data type delimiters. No delimiters are needed for numeric values.
|
Access Table 2-1 Criteria operators Operator Function Examples = Return records that match the value exactly. Using the = sign is optional. = 1000 > Return records that have a field value greater than the value in the criterion. For text data, letters that are later in the alphabet are greater than earlier letters. > 5 >= Return records that have a field value greater than or equal to the value in the criterion. >= 5 < Return records that have a field value less than the value in the criterion. < 5 < “Jones” <= Return records that have a field value less than or equal to the value in the criterion. <= 5 <= “Jones” <> Return records that have a field value that is not equal to the value in the criterion. <> 5 Between Return records with a field value that is between the two stated values. Between 10 And 20 In Return records that match one of several values in a list. In(2, 4, 6, 10) Like Return records that match the pattern of the entry with wildcards. * can be replaced by many characters. ? can be replaced by one character only. Like “Mortgage*” |
Making an And Query
Sometimes we wish to ask questions that are a bit more complex. For example, we may wish to ask something like:
What, if any, loans have an interest rate greater than 5% and a loan minimum less than $10,000?
These questions require more complex queries, for which we will need to set criteria on more than one field. For the first question, we would need to filter for records that have the field LoanRate > 0.05 and the field LoanMin < 10000. Note the use of the word and in the previous sentence. And is a logical operator corresponding to the intersection of two sets, shown in your math classes as the overlap of two circles in a Venn diagram. This means that when And is used to connect two criteria; both of the criteria must match before a record is shown in the query. If either one of the query criteria is not a match, then the record is filtered out and not shown in the query.
|
Thinking Critically: Draw a Venn diagram with two circles. Imagine that the circles contain the data that resulted from two Access queries. Shade in and label the area that would represent the data returned by an Or query and the area that would represent the data returned by an And query. Relative to each other, which type of query, Or or And, would generally include the broadest set of results? |
To create an And question in the Query Design form, simply place each of the criteria on the same row in the criteria grid.
|
1. Start a query with the design shown in Access Figure 2-30. Include the LoanType, LoanRate, and LoanMin fields.
Access Figure 2-30 Start of query design. 2. For the LoanRate field, add the criterion: >0.05, as shown in Access Figure 2-31.
Access Figure 2-31 LoanRate > 5% criterion. This criterion will filter the records to show only those with a LoanRate greater than 5%. 3. For the LoanMin field, add the criterion <10000, as shown in Access Figure 2-32. Access Figure 2-32 LoanMin <$10,000 criterion. Alone, this criterion will filter the records to show only those with a LoanMin less than $10,000. Together with the LoanRate criterion, the two criteria will filter the records to display only those records with a LoanRate < 5% and a LoanMin > $10,000. 4. To see the query results, click the Run command on the Query Tools Design ribbon as shown in Access Figure 2-33.
Access Figure 2-33 Using Run to get query results. 5. Your query results should be like those shown in Access Figure 2-34.
Access Figure 2-34 AND query results. Notice that the values should correctly fit both criteria. 6. Click on the X in the upper left corner of the query Datasheet window.
Access Figure 2-35 Prompt for saving the query. 7. Save the query with the name Loan_Min_<10K_and_Loan_Rate_>5% as shown in Access Figure 2-36.
Access Figure 2-36 Naming the query. |
Making an Or Query
In our last example, we looked at a query that uses an And structure for the filter criterion. That is, a record was displayed only if it satisfied both criterion 1 and criterion 2. Another type of question that is often asked of the data is known as an Or query. For example, we may wish to ask:
|
Which loans are for automobiles or have an interest rate greater than 5%? |
For an Or question, a record will need to satisfy one criterion, the other, or both to be displayed. For our example, a data record will satisfy this question if it is an auto loan, if it has an interest rate more than 5%, or both. To set up an Or query, we simply place our criteria on different rows in the criteria section of the QBE form.
In this next example, we’ll see how to set up an Or query. We’ll also see how we can set a Like criterion on a field with the text data type.
|
1. Start a query with the design shown in Access Figure 2-37. Include the LoanType, LoanRate, and LoanMin fields.
Access Figure 2-37 Starting the Or query. 2. In the first Criteria row under the LoanType field, type: Like “Auto*” You must include the quotes and the asterisk (*) as shown in Access Figure 2-38.
Access Figure 2-38 Settting a Like criterion on LoanType. This criterion will match all records that start with the text Auto. 3. Notice that all of the records in the current query results shown in Access Figure 2-39 have a LoanType that starts with Auto.
Access Figure 2-39 Results of Like query. Let’s go back to Design view to finish the query. 4. In the second criteria row below the LoanRate field, type: >5% We placed this criterion in a different row so that we can make an Or query.
Access Figure 2-40 Adding the Or criterion. Click the Run command to see the results so far. 5. Wait a minute! We got an error message (shown in Access Figure 2-41)!
Access Figure 2-41 Invalid syntax error. Whenever you get a message, be sure to read it and try to understand it before dismissing it. They generally give you a clue about what you need to fix. Thinking Critically: Before moving on, what do you think caused this error? 6. Recall that the LoanRate field was defined with a Number data type. >.05 Make the change, as shown in Access Figure 2-42, and run the query again.
Access Figure 2-42 Correcting the LoanRate criterion. 7. You should see results like those shown in Access Figure 2-43.
Access Figure 2-43 Or query results. Notice that we now have four records displayed in the query results. |
Building a Query Expression
Sometimes we want to ask questions whose answer is not explicitly stored in the database but rather is derived from the data. For example, with our database we might want to answer:
|
What is the average of the loan rates offered by our bank? |
It wouldn’t make sense to actually store the average in the database because it would be incorrect as soon as we added or deleted a new loan type or changed the rate on a loan type. It makes more sense simply to use the loan rates that are currently stored in the database to calculate the current average when we need it. We can do this in a query by building expressions into our query. Let’s see how we can do this.
Keep in mind that we are building a fairly simple expression with this example. With a little imagination you should be able to see that you can follow similar steps to build more complex expressions when needed.
|
1. Start a query with the design shown in Access Figure 2-44. Include the only the LoanRate fields.
Access Figure 2-44 Starting the query design. We don’t really want to display a loan rate in the results. We just want to have the LoanRate field available for our expression. 2. Click in the next field cell of the QBE form to make it active.
Access Figure 2-45 Starting the Expression Builder. 3. The Expression Builder dialog will appear.
Access Figure 2-46 The Expression Builder dialog. Of the windows on the bottom, the left window lets you navigate through folders where components for your expression may be stored. 4. The dialog should now appear as shown in Access Figure 2-47.
Access Figure 2-47 Built-in expression functions. In the center window, you now see categories of built-in functions. 5. Now you’ll see that you have started to build an expression. The text Avg(expr) is shown in the upper window of the dialog (Access Figure 2-48).
Access Figure 2-48 The Avg function. The 6. In the upper window of the dialog, highlight
Access Figure 2-49 The Avg function applied to LoanRate. In this case, the expression that we are supplying to the function is simply the name of a field in the database. 7. Click OK on the Expression Builder dialog.
Access Figure 2-50 The expression in the QBE form. Take a good look at how the expression looks and then run the query to see the results so far. 8. Looks pretty good!
Access Figure 2-51 The expression results so far. Notice that the average rate is shown as a decimal number and that the field heading says Expr1. It would be nice to make the value and the heading more meaningful. Thinking Critically: How could you check to see whether this query is working correctly? 9. Return to Design View for the query.
Access Figure 2-52 Adjusting the Expression field. Select Properties. 10. A dialog will appear on the right of the Design window that you can use to adjust the properties of this field.
Access Figure 2-53 The Expression field properties. Before moving along, take a moment to look at the other properties that you could adjust here. 11. The easiest way to change the displayed field heading is simply to edit it in the QBE form.
Access Figure 2-54 Editing the field heading. 12. After running the query, you’ll see the results shown in Access Figure 2-55.
Access Figure 2-55 The final query results. The value is the same, but what you are looking at makes a little more sense with the new heading and the % symbol as clues. Loan_Rate_Average. |
Communicating Results Using Access Reports
Reports are snapshots of selected parts of your data. The contents of a report are based on the criteria you specify; for example, base the report on a table or a query. When you create an Access report, you are basically defining the fields and records in the database that will be a part of the report, and you are defining formatting options that will determine how your report will look. Reports display the data in only those fields that you included when you built the report. If you left out a field in a table or a query, it will be present elsewhere in your database but it will not appear on your report.
You can use reports to organize and view your data, and you can share these views with others. Reports and queries are two ways to create information from your data. In this section we’ll look at how we can create a report based on our loans table. The same process can be used to create a report from a query.
|
1. To begin, open the loans table.
Access Figure 2-56 Starting to create a report. 2. A report is generated and is shown in the report Layout view as in Access Figure 2-57.
Access Figure 2-57 The initially created loans report. Notice that the report is showing the data that is currently in the table. 3. Right-click on the column heading for the LoanID on the report.
Access Figure 2-58 Deleting LoanID from the report. The LoanID column will disappear from the report and the other fields will adjust to the left. 4. Let’s make the report more attractive.
Access Figure 2-59 Selecting an AutoFormat. Choose a look that you think is attractive. 5. Let’s also sort the records on the report in increasing order of LoanRate. Click the box at the bottom of the window labeled Add a Sort as shown in Access Figure 2-60.
Access Figure 2-60 The AutoFormatted report. 6. A list of the fields that are available in the report is displayed.
Access Figure 2-61 Sorting the report records by LoanRate. If you wanted, you could add second and third levels of sorting to the report. We’ll stop with one. 7. We should also make the report title a bit more descriptive.
Access Figure 2-62 Editing the Report Title. 8. You can edit the column headings in the report in the same way.
Access Figure 2-63 Final report in Layout view. Let’s have a look at the final report. 9. The Report view is shown in Access Figure 2-64.
Access Figure 2-64 The final report in Report view. This is how the report will appear if displayed on the screen. 10. The Print view shows the report as it will look when printed.
Access Figure 2-65 The final report in Report view. |
|
Thinking Critically: What more could you do to the report to make it better? Give some of your ideas a try. |
|
Exercises to Build Your Knowledge of MS Access Match each of the Access terms with its description. 1. _________ AND query 2. _________ Detail query 3. _________ Expression Builder 4. _________ OR query 5. _________ Query 6. _________ Query by example (QBE) 7. _________ Report 8. _________ Structured query language (SQL) 9. _________ Summary query 10. _________ Wildcard
(a) A tool for selecting data from a database that can help you answer a specific question. (b) A tool for presenting the data in a database by preparing a nice-looking template. (c) An Access dialog that helps you build query fields that are calculated or derived from data stored in the database. (d) A query that shows all data that satisfies the query design and criteria. (e) A query in which the data that satisfies the query design and criteria is aggregated into values such as totals, averages, minimum values, and maximum values. (f) The use of a form to create the design for a query rather than a text description. (g) A standard, textual language that can be used to create query designs. (h) A query that uses two or more filter criteria. A record will satisfy the criteria only if it simultaneously satisfies all of the criteria. (i) A query that uses two or more filter criteria. A record will satisfy the criteria if it satisfies just one of the criteria. (j) A special character such as ? or * that can be included in a query filter and used to represent any character or string of characters. Follow the directions in this Access tutorial to create and execute queries to the loans table discussed in the tutorial using QBE. In addition, you should create and run queries using QBE to answer these questions: 11. Which loans have an interest rate less than 6%? 12. Which loans have a LoanType of “Student Loan”? 13. Which loans have a loan minimum between $5,000 and $10,000? 14. Which loans are either a Mortgage loan or an Auto loan? 15. Which loans have an interest rate less than 5% and a loan minimum greater than $10,000? 16. Copy the ACC_2_Bank_Database_Example.accdb file to a new folder and complete the following exercises (do not modify the original version of the file because you will need it for future tutorials). LoanType LoanRate LoanMin Personal 12% $1,000 Credit Card 18% $10 Once you do this, open your customer report (rptLoan). Has it been updated? 17. Now go back to the loans table and add a LoanTerm field between LoanType and LoanMin. Once you’ve done this, give all loans in the database the appropriate term length in months (for the student loan, make it 120 months; for the personal loan, make it 60 months; and for the credit card loan, make it 12 months. All other loan terms should be evident to you.) Close the table and open the loans report. Is it updated? Hmmmm. Why not? See if you can use the Report Design or Layout view to add the new field to the report. Access Table 2-2 stocks table fields Field Name Data Type Description stockSymbol Text A text symbol up to 5 characters long. stockName Text The full name of the stock. purchaseDate Date/Time The date when the block of stock was purchased. purchasePrice Currency The price per share at which the block of stock was purchased. numberOfShares Number (Integer) The number of shares in the block of stock. Answer the following questions (keep in mind that you could create an example database in Access to help you obtain the answers): 18. What field(s) and criterion(criteria) would you use in a query designed to answer the questions: “Which stocks were bought before August 2007?” 19. What field(s) and criterion(criteria) would you use in a query designed to answer the questions: “Which stocks were bought before August 2007 that has a stock purchase price less than $100 per share?” 20. What field(s) and criterion(criteria) would you use in a query designed to answer the questions: “Which stocks were bought before August 2007 or after December 2007?” 21. Suppose you wanted a query that included a derived field showing how much a block of stock was worth when purchased. Describe how you would set up this query: 22. Suppose that over the last year you have made several separate purchases of a stock with the symbol IBM. Describe how you would design a query that showed you the average purchase price per share, the total number of shares owned, and the number of different purchase transactions for this stock. Use Access Help to find the answers to the following questions: 23. If applied to a Text field in a query, what would the criterion Is Not Null And <> show us? 24. When is it possible and when is it not possible to edit data with a query? 25. What is report grouping used for? |
|
Access Tutorial 2: Mini-Case 1
Scenario: File: Your Task: · What are the names, departments, and salaries of all employees? (Save this query as EmployeeList.) · What are the names of all of the employees in the Accounting department? (Save this query as AccountingList.) · What are the names and departments of employees who have an annual salary of at least $100,000? (Save this query as 100000.) · What are the names, departments, and birthdates of employees with an annual salary between $30,000 and $50,000? (Save this query as 30kto50k.) · Who are the employees who have worked at the company since the year 2000? (Save this as after2000.) · Who are the employees who have worked at the company since the year 2000 and have an annual salary at least $35,000? (Save this as after 2000and35k.) · List the employees that are in both the Finance (FIN) and Operations (OPS) departments. (Save your query as FINorOPS.) · Which employees will have been with the company for more than 10 years at the start of 2007? (Save your query as 10Years.) · Which employees will have been with the company for more than 10 years or will be over 30 years of age as of the start of 2007? (Save your query as 10Yearsor30.) Make a report based on salary30kto50k. Group the data by the birth date and sort the data within groups by salary. |
|
Access Tutorial 2: Mini-Case 2
Scenario: Access Table 2-3 Tasks table fields Field Name Data Type Description taskDesc Text A description of the task that needs to be done. taskPriority Number (Integer) How important the task is relative to others in the database. Stored as a number from 1 to 5 with 1 being the most important. taskDueDate Date/Time The date when the task needs to be completed. For some items there may be no due date. taskCompleted Yes/No Whether or not the task has been completed. taskCategory Text The number of shares in the block of stock. File: Your Task: · What items are due within the next week? (Save this query as weeklyTasks.) · What tasks are stored for the “school” category? (Save this query as schoolTasks.) · Are there any tasks with a priority of 1 that are due today? (Save this query as highToday.) · How many tasks for the “Work” category have been completed? (Save this query as workDone.) · How many tasks from last week were completed? (Save this as lastCompleted.) · Which tasks are stored that have no due date or a priority of 5? (Save this as lowTasks.) Also, create a report based on one of the queries that will show the tasks for the next week in order of due date and priority. You should make up data and enter it into your table to test the components that you create. |
Tutorial 3: Working with Tables in Microsoft Access 2007
|
After reading this tutorial and completing the associated exercises, you will be able to: · Work with related tables in Access using primary and foreign keys. · Set input masks and LookUp fields. · Implement referential integrity between tables. · Create QBE queries for related tables. Files for Download: Files associated with this tutorial for you to download include: · The tutorial example template file, ACC_3_Bank_Database_Example.accdb Starter File: Tutorial 3: Bank Database Example |
Microsoft Access: A Relational Database Application
Access 2007 Animated Tutorials: Indexing
Since Microsoft Access is a relational database application, it can work with more than one table. The relational model enables Access to store data in tables and then link tables via common fields. For example, assume we have the same banking application as covered in the first two Access tutorials. In that case, in addition to the loans table discussed earlier, we might have two other tables—say, a table of customer information, named customers, and a table of information on which customers have which loans, named customerLoans. In this case, the primary key (LoanID) field in the loans table and a copy of the LoanID field (a foreign key) in the customerLoans table are used to link these two tables. Similarly, the primary key in the customers table (CustomerID) and a copy of it in the customerLoans table (a foreign key) are used to link these two tables. In the process, the loans and customers tables are also linked.
Note that a customer’s information is stored only once, in the customers table. This reduces data redundancy and improves updating and other database functions. The decision as to exactly which tables to create is discussed in more detail in the Appendix on Data Modeling that is part of the Access Tutorial.
As with other tutorials, most of this tutorial will be spent learning by building. First you will expand the BankDatabaseExample database by adding the customers and customerLoans tables. You will then learn how to link the tables by creating relationships. Once the tables are linked, you will learn how to query a multi-table database and to create reports from it.
Building Multiple Tables
To work with multiple tables, you first need to start MS Access and open the ACC_3_Bank_Database_Example.accdb file. From the database window, click the Table command on the Create ribbon to begin creating a new table named customers in the Design view. The required field names and data types for the customers table are shown in Access Table
3-1
, and the description for the customerLoans table is provided in Access Table
3-2
. You should use this information to create the two new tables in the database. Be sure to define a primary key for each table.
|
Access Table 3-1 customers table fields Field Name Data Type Comments custID Text 9-digit social security number (primary key) custLastName Text Last name custFirstName Text First name custBirthDate Date/Time Used for promotional purposes custEMail Text Point of contact for customer |
|
Access Table 3-2 customerLoans table fields Field Name Data Type Comments custLoanID AutoNumber (primary key) custLoanStartDate Date/Time Date when the loan began custLoanAmount Currency Amount of loan custLoanStatus Text Status of loan (Current, Late, Paid-in-full, etc.) FKcustID Text Foreign key from the customer table FKloanID Number Foreign key from the loan table |
Repeat the table creation process to create the fields for the customerLoans table shown in Access Table 3-2.
Notice in Access Table 3-2 that we have two fields in this table with the prefix FK. These two fields are foreign keys that correspond to the primary keys in the customers and loans tables, respectively. One rule of relational databases is that when there is a one-to-many relationship between two entities in the data model, the primary key of the entity on the “one” side of the relationship will be used as a foreign key in the table on the “many” side of the relationship. These foreign keys should already be listed in the logical data model that you are using for your blueprint. The important thing to watch out for here is that you need to make sure the data type used for a foreign key field is compatible to that used when the attribute is used as a primary key. After adding the two tables just described, your database will have three tables as shown in the navigation pane displayed in Access Figure 3-1.
|
Access Figure 3-1 Tables in BankDatabaseExample database. |
Input Masks
In regard to formatting fields, an input mask is a useful tool. An input mask prevents users from such errors as entering improper characters, too many characters, or forgetting a character. In other words, input masks help the users enter the correct data, thereby improving the accuracy and integrity of the data in our database. Before we enter data into the tables, let’s make a few changes to the basic table design by adding input masks to some fields. For example, we will show you how to create a social security number input mask for the custID field and a short date input mask in the customers table.
1.
Start by going to the Design view of the customers table.
The first field that we want to set an input mask for is the custID field. So select it by clicking on the custID field name.
In the field properties form, click in the Input Mask field. A button with an ellipsis (…) will appear as shown in Access Figure 3-2. Whenever you see this type of button, a dialog will appear when you click it.
Access Figure 3-2
The customer table in Design view.
2.
In this case, the Input Mask Wizard dialog appears as shown in Access Figure
3-3
.
Access Figure 3-3
The Input Mask Wizard dialog.
An input mask basically defines a pattern that the data must fit.
On the first page of the Wizard you’ll find a list of patterns that represent the most commonly used input masks for the data type.
Select the Social Security Number pattern and click Next.
3.
The pages in the Wizard may appear differently for different types of patterns.
Here, we are presented with a text box in which we can make adjustments to the pattern. This is currently beyond our knowledge, so we’ll rely on the built-in pattern.
We can also select a placeholder character that the user will see when entering data into the field.
Since the user will be entering numbers, let’s select the # symbol for the place holder, as shown in Access Figure
3-4
, and click Next.
Access Figure 3-4
Setting a placeholder.
4.
On the next page of the Wizard we are presented with choices for how the data will be stored.
We’ll choose to store the data with the symbols so the social security number will have hyphens wherever it appears.
Select the option that reflects this choice, as shown in Access Figure 3-5, and click Next.
Access Figure 3-5
Selecting storage format.
Thinking Critically:
Assume that one day your bank will be as big as the Bank of America. The Input Mask Wizard asks you whether you want to store the “-” symbols or just the numbers. What are you going to choose? Why?
5.
The final page just indicates that you are finished.
So, unless you want to go back and adjust something, click Finish as shown in Access Figure 3-6.
Access Figure 3-6
Last page of Input Mask Wizard.
6.
Another likely candidate for an input mask is the custBirthDate field.
Select this field and pull up the Input Mask Wizard.
Notice that the patterns available for the Date/Time data type are different from those for the Text data type.
Select the Short Date pattern and click the Finish button as shown in Access Figure 3-7.
Access Figure 3-7
Selecting Date/Time input mask.
Important! When working with multiple tables, it’s a “best practice” to wait until the relationships between the tables have been defined (see a later section in this tutorial) before entering data. However, we want to show you the results of the input masks that you just created, so we are going to violate that practice just this once. Usually, it’s not a good idea to do this, and we (the authors) will follow the “best practices” when creating our own databases. For now though, please bear with us.
7.
With the input masks set, go to the Datasheet view of the customers table as shown in Access Figure 3-8.
Access Figure 3-8
Switching to Datasheet view.
8.
Notice that when we make changes to the table design, these need to be saved before we can switch views.
Click Yes when asked to save the table, as shown in Access Figure 3-9.
Access Figure 3-9
Saving the customers table.
9.
Click in the custID field and start typing a social security number. (Make one up; don’t use your real one!)
Notice that a pattern appears showing you where to type the digits, as shown in Access Figure 3-10.
Access Figure 3-10
Typing data into a field with an input mask.
Remember that you selected the # sign to designate where to type when you set up the input mask.
10.
Type a last name (we used Trump) and a first name (ours is Daffy).
Now enter in a birth date.
Notice again that an input mask guides how you can enter the date data, as shown in Access Figure 3-11.
Access Figure 3-11
Entering data into a date input mask.
11.
Go ahead and add a couple more records, as shown in Access Figure 3-12, to get used to how the input masks work.
Access Figure 3-12
Entering a few more records.
Can you see how input masks can be helpful in making sure that users enter valid data?
Lookup Fields
Let’s adjust a couple of fields in the customerLoans table to make data entry a little more user friendly. Specifically, we will change the data type of the foreign key fields so that the user entering data can easily look up a record in a related table that they want to reference. Recall that a foreign key is primarily a reference to a record in another table. Since the primary key of the record in the other table is a unique value, whenever that value is used in a foreign key, it serves as a reference to that single record.
A value that we want to enter into a foreign key field must already exist as a primary key value in the related table. One difficulty in entering foreign key values is that it can be difficult to remember the possible values available to enter. This is especially true if the primary key has the AutoNumber data type (automatically generated by Access).
One way to overcome this difficulty is to allow the user to actually look up values from a list when entering the foreign key. Access provides the Lookup Wizard data type just for this. Let’s see how we can use this to improve data entry from the customerLoans table.
|
1. Pull up the customerLoans table in the Design view.
Access Figure 3-13 Selecting the Lookup Wizard data type. 2. You have the option of looking up values that exist in another table or typing in values that you want the user to see.
Access Figure 3-14 First page of the Lookup Wizard. 3. On the next page of the Wizard you’ll see a list of the tables in the database.
Access Figure 3-15 Selecting the lookup table. 4. You’ll now see the fields that are available in the selected table.
Access Figure 3-16 Selecting the lookup fields. We’ll see why in a moment. 5. On the next page you can choose to sort the lookup list based on one of the fields.
Access Figure 3-17 Sorting the lookup list. 6. Next you’re asked how you want the lookup to look.
Access Figure 3-18 Adjusting the lookup look. The remaining list that you see is what the user will see when entering data. 7. The final page of the Lookup Wizard will ask two questions. Second, we can choose to store all of the values.
Access Figure 3-19 Final page of Lookup Wizard. 8. Switch over to the Datasheet view of the customerLoans table, saving the table when asked.
Access Figure 3-20 Entering data using a lookup. Select the customer last name from the list. 9. Notice that we have a date field in the customerLoans table, as shown in Access Figure 3-21. We also have another foreign key field: FKloanID.
Access Figure 3-21 The customerLoans Datasheet view. Test your skills by adding input masks and/or lookups to these fields as appropriate. |
Table Relationships
Relationships are a very important part of a relational database. In fact, you might say that they are what make the database, well, relational. Relationships define how the tables in your database are related. As you may have read or will read in our data modeling tutorial, a relational database may contain relationships that are either one-to-one or one-to-many. Recall that a one-to-one relationship means that a record in one table may be related to one and only one record in the related table. One-to-many means that a record in one table may be related to one or more records in the related table.
The foreign keys in the database exist solely for making these relationships. A foreign key provides a reference in one table to a record that is stored in another table. Determining what relationships should be in the logical model and which tables need foreign keys is part of database design, which you can read about in our data modeling tutorial. For now, we need to see how we can specify our intended relationships while we are creating the database in Access.
For our example, we have two relationships. The first is a one-to-many relationship between the loans table and the customerLoans table. A record in the loans table can be related to one or more records in the customerLoans table. That is, there can be many customer loans for a single loan type. The second is a one-to-many relationship between the customers table and the customerLoans table. In other words, a single customer may have one or more loans.
Creating Relationships with Referential Integrity
Access 2007 Animated Tutorials: Referential Integrity
In Access, you create relationships by specifying a connection between a primary key in one table and a foreign key in another. Let’s see how that is done for our example.
|
1. Open the database and navigate to the Database Tools ribbon.
Access Figure 3-22 The Relationships command. Click it! 2. A blank workspace will appear with a tab labeled “Relationships,” as shown in Access Figure 3-23.
Access Figure 3-23 The Relationships workspace. Our first step will be to add the tables that we want to connect with relationships. 3. The Show Table dialog will appear with a list of the tables that are available in the database.
Access Figure 3-24 The Show Table dialog. 4. After selecting a table, a box representing the table will appear in the Relationships workspace.
Access Figure 3-25 Showing the tables. 5. You should see the table boxes displayed in the workspace in the order in which you chose them.
Access Figure 3-26 Tables on the relationships workspace. 6. Now drag that box out of the way so we that you can move customerLoans to the center as shown in Access Figure 3-27.
Access Figure 3-27 Moving the table boxes. Using the same method, drag the customerLoans table to the center and arrange the customers table on the left.
Access Figure 3-28 The relationships table arrangement. 7. It doesn’t matter that the boxes line up perfectly. We are moving them for convenience only. 8. Drag the custID field from the customers table over the FKcustID field in the customerLoans table.
Access Figure 3-29 Edit Relationships dialog. 9. A line connecting the two, now-related tables will appear as shown in Access Figure 3-30.
Access Figure 3-30 The newly added relationship. Since we enforced referential integrity, you should also see that the line is labeled with a 1 and ∞, signifying a one-to-many relationship. 10. Go ahead and create a relationship between the customerLoans table and the loans table.
Access Figure 3-31 Edit Relationships dialog. The completed relationships for our example are shown in Access Figure 3-32.
Access Figure 3-32 Completed relationships. |
Why Enforce Referential Integrity?
You should think of the values stored in a foreign key as a reference to a record in another table. For example, a value of 10 in the FKcustID field of the customerLoans table is a reference to the unique record with custID = 10 in the customers table. Enforcing referential integrity helps us to make sure that there are no references in our database to nonexistent records. If we try to add a record to customerLoans that tries to reference a custID that does not exist in customers, Access will display a message to warn us about it and will not add the erroneous record. Conversely, if we try to remove a customer record from customers and that customer has outstanding loans listed in customerLoans, we would also get a warning message and would not be permitted to delete the customer. This feature helps to maintain the overall integrity of our database.
Entering Data
You are now ready to enter data into the customers and customerLoans tables. Sample data for you to use is shown in Access Tables 3-3 and 3-4. You can add data directly using the Datasheet view, or better yet, create a couple of form objects and use them.
|
Access Table 3-3 customers data custID custLastName custFirstName custBirthDate custEMail 765-56-6765 Rockefeller Horatio 12/12/1958 horatio@mymail.com 789-76-5432 Carnegie James 4/27/1963 jamesc@gmail.com 812-81-0028 Trump Daffy 3/21/1954 dtrump@mol.com 999-11-1111 Patrick Chris 04/27/1973 cpatrick@abac.not 999-22-2222 Mullins Jesse 07/24/1985 jmullins@vstate.not 999-33-3333 Crider Russ 08/22/1974 rcrider@uga.not 999-44-4444 Goodly Alice 01/31/1962 agoodly@negia.not 999-55-5555 Roberts John 03/15/1977 jroberts@fsu.not 999-66-6666 Carrall Ann 11/15/1978 acarrall@aow.not 999-77-7777 Arons Suzy 09/07/1944 sarons@uf.not 999-88-8888 Allman Keegan 06/08/1972 rrand@fgcu.not 999-99-9999 Hyatt Ashley 08/22/1969 ahyatt@uncch.not |
|
Access Table 3-4 customerLoans data custLoanStart Date custLoan Amount custLoan Status Customer (FKcustID) Loan (FKloanID) 06/01/2004 $200,000.00 Current Carnegie 15 Year Mortgage 01/09/2005 $150,000.00 Current Patrick 15 Year Mortgage 01/15/2005 $14,000.00 Paid in Full Crider Auto Loan 48-Month 02/20/2005 $280,000.00 Current Mullins 30 Year Mortgage 05/22/2005 $28,000.00 Current Hyatt Auto Loan 60-Month 10/10/2005 $3,500 Current Roberts Student 03/13/2006 $125,000 Current Goodly 30 Year Mortgage 05/15/2006 $11,500 Current Carrall Auto Loan 48-Month 08/31/2006 $3,500 Delinquent 30 Days Crider Student 09/30/2006 $15,500 Delinquent 90 Days Roberts Auto Loan 60-Month 11/22/2006 $19,500 Current Crider Auto Loan 60-Month |
Querying a Relational Database
Now that you’ve created a relational database composed of three related tables, you are ready to query that database. Creating queries involving multiple tables with the MS Access Query by Example (QBE) facility works just like it does for one table—you add all of the tables needed for the query to the QBE window, select the fields from one or more tables that you wish to see in the query, and then add criteria.
For example, let’s create a query that shows us the names of customers who have a loan with an amount that is greater than $30,000. The data with this information is found in two tables: the names in the customers table and the loan amount in the customerLoans table. We can create this query as follows.
1.
Open the database and click the Query Wizard command on the Create ribbon as shown in Access Figure 3-33.
Access Figure 3-33
Starting the Query Wizard.
2.
The New Query dialog will appear.
Select Simple Query Wizard from the list and click OK as shown in Access Figure 3-34.
Access Figure 3-34
The New Query dialog.
3.
On the next page of the New Query Wizard, you can select the fields that we want to be part of the query.
Select the customers table from the Tables/Queries list as shown in Access Figure 3-35.
Access Figure 3-35
Selecting the customers table.
4.
A list of the customer table fields will be displayed in the Available Fields list.
Highlight the custLastName field and click the right arrow to add it to the Selected Fields list as shown in Access Figure 3-36.
Access Figure 3-36
Selecting custLastName field.
5.
Use the same technique to add the custFirstName field from the customers table and then the custLoanAmount from the customerLoans table.
Click Next to go to the next page of the Wizard as shown in Access Figure 3-37.
Access Figure 3-37
Adding the remaining query fields.
6.
Select Detail Query and click Next as shown in Access Figure 3-38.
Access Figure 3-38
Selecting detail query.
7.
On the final page, name the query loans>30K.
We’re not really through designing the query, but that is all we can do with the Query Wizard.
Select “Modify the query design” before clicking Finish as shown in Access Figure 3-39.
Access Figure 3-39
Naming the query.
8.
You’ll then see the QBE query design as shown in Access Figure 3-40.
Access Figure 3-40
The Query Design
To complete the query, we need to add the criterion
> 30000
to the custLoanAmount field.
Do that and then click the Run command on the Design ribbon under Query Tools, as shown in Access Figure 3-40.
9.
You should now see the query results as shown in Access Figure 3-41.
Access Figure 3-41
The query results.
Notice that all loan amounts are above $30,000.
Remember that this query drew results from two different but related tables.
Queries that draw data from two or more tables are known as join queries.
Let’s adjust the design to add some data from the loans table as well.
10.
In Design view, right-click anywhere in the workspace to get the context-sensitive menu shown in Access Figure 3-42.
Access Figure 3-42
QBE context-sensitive menu.
Have a look at the options available.
For now, select Show Table.
11.
You’ll see the Show Table dialog again with the list of tables.
This time, select the loans table and click Show.
After it appears as shown in Access Figure 3-43, close the dialog.
Access Figure 3-43
Adding the loans table to the query.
12.
Now add the loanType and loanRate fields to the query.
Finally, click the Run command as shown in Access Figure 3-44.
Access Figure 3-44
Adding fields from the loans table.
13.
Your results should appear like those shown in Access Figure 3-45.
Access Figure 3-45
The three-table query results.
Now we have a query that draws from three different but related tables.
Using these techniques you can create the more complex queries like those discussed in Access Tutorial 2.
|
Exercises to Build Your Knowledge of MS Access Match each of the Access terms with its description. 1. _________ Input mask 2. _________ Join query 3. _________ Lookup field 4. _________ Referential integrity 5. _________ Relationship
(a) A query that draws data from two or more related tables. (b) A pattern you can set on a field to make sure that the user enters data in a valid format. (c) Setting a data type so that the user can select the value to enter in a field from a list of available values. (d) A connection between two tables that is created by linking the primary key of one table to a corresponding foreign key in another table. (e) A feature that ensures that the foreign key values entered into a database are valid references. 6. Follow the directions in this Access tutorial to add the customers and customerLoans tables to the ACC_3_Bank_Database_Example.accdb database file. Be sure to include creating the relationships between the tables and then adding the data shown in Access Tables 3-3 and 3-4. 7. Follow the directions in this Access tutorial to use QBE to create and execute all of the queries to the tables in the ACC_3_Bank_Database_Example.accdb database discussed in the tutorial. In addition, you should create and run queries to display the following information: 8. List all customers with a loan having an interest rate greater than 6%. 9. List the e-mail address and birth date of all customers with a loan amount greater than $50,000. 10. List the SSNs and first and last names of all customers who are delinquent on their loans. Also include the loan amount and loan type. Use Access Help to find the answers to the following questions: 11. An input mask can contain three sections. What are they? 12. What are a bound value and a display value in a lookup column? 13. What is an inner join? Do the queries discussed in the tutorial use inner joins? 14. When you choose to enforce referential integrity, what does Access do to make sure that references are correct? 15. On the Database Tools ribbon next to the Relationships command in the Show/Hide group there is a command called Object Dependencies. What is an object dependency and what is shown when you click this command? |
|
Access Tutorial 3: Mini-Case 1
Scenario: Your Task:
Access Figure 3-46 Book inventory database. Create data entry forms for your database. Make one form for each table. · What authors (first and last name) wrote what books (title, year published, and description) by what publisher (publisher name)? (Save your query as bookList.) · Choose one of the authors in your database. What publishers published books by this author)? (Save your query as authorPub.) · What books if any have more than one edition (title, author, publisher, edition, year published)? (Save your query as edition.) · What authors wrote a book that was published before 1990? (Save your query as 1990Entries.) Create a report based on the query bookList. Group by authors’ last names. Subgroup by publisher name. |
|
Access Tutorial 3: Mini-Case 2
Scenario:
Access Figure 3-47 Car maintenance database. Your Task: · Which customers use which cars and at what daily rate? · What is the total daily rate for all cars? · Which mechanics worked on a car for more than 5 hours? · Which cars have been worked on in the last 3 months? · Which customers still owe for repair work? (Create a query and a report based on the query.) |
Tutorial 4: Microsoft Access 2007 and SQL
Learning Objectives
After reading this tutorial and completing the associated exercises, you will be able to:
·
Discuss the use of SQL to query a relational database.
·
Find matching records using a SELECT query.
·
Carry out computations using aggregate functions.
·
Insert, delete, or change records into a table.
·
Use SQL with multiple tables.
Files for Download: Files associated with this tutorial for you to download include:
·
The tutorial example template file, ACC_4_Bank_Database_Example.accdb
·
The mini-case template file, ACC_4_MC_Students. accdb
Starter File: Tutorial 4: Bank Database Example
Starter File: Tutorial 4: Mini-Case Students
Learning About SQL
For a relational database, the instructions for finding records are written in the form of queries in Structured Query Language (SQL), which is a computer language for manipulating data in a relational database. SQL queries also enable database users to add new records or change or delete records in a database that meets some stated criterion. The Query By Example (QBE) system that you have been using to query databases takes the design you give it and converts it into SQL when it actually queries the database.
The general form of an SQL query to search for matching records is:
SELECT fields FROM tables WHERE fields match query condition
In the SQL statement, the SELECT keyword designates which fields will be displayed as a result of the query, the FROM keyword designates which tables are to be searched, and the WHERE keyword specifies the search criteria or query condition to be used in finding records. In our use of SQL, we will put keywords in uppercase to make them stand out. In practice, you don’t necessarily type them in uppercase.
In addition to the SELECT keyword, there are keywords that can be used to CREATE a table, INSERT new records in a table, DELETE records from a table, and to UPDATE one or more records in a table. We can also search for records that are like a specific condition as well as computing sums, averages, and so on, for all records that match some criteria.
SQL with One Table
In our discussion of SQL, we will use the same example as shown earlier—the ACC_4_Bank_Database_Example.accdb file. We start by creating a query and then switching to the SQL view as follows.
1.
Start by clicking the Query Design command on the Create ribbon.
Close the Add Tables dialog.
Your Access Window should now look like that shown in Access Figure
4-1
.
Access Figure 4-1
Selecting SQL view.
On the Design ribbon under Query Tools, click SQL View.
2.
The workspace will change to look as shown in Access Figure
4-2
.
Access Figure 4-2
The SQL view.
This workspace is basically a simple text editor that allows you to type an SQL command.
As you’ll see, most SQL queries begin with the SELECT keyword, so that is provided for you.
To use SQL to display the loanType, loanRate, and loanMin fields from the loans table, as we did using QBE in Access Tutorial
2
, the SQL command is:
SELECT loanType, loanRate, loanMin FROM loans
and the resulting output would display information on all of the loan types. To sort these in order of loanRate, you simply add the clause ORDER BY loanRate to the end of the SQL statement.
3.
Type the query as shown in Access Figure
4-3
.
Access Figure 4-3
SQL query to display loanType, loanRate, and loanMin in order of loanRate.
Click the Run command on the Design ribbon.
4.
The results of your SQL query will look like those shown in Access Figure
4-4
.
Access Figure 4-4
Results of SQL loans query.
Follow this same procedure for each of the SQL queries described in this tutorial.
Fact: You can replace the list of fields with the asterisk if you want to display all of the fields in a table. For example, to display all of the fields in the loans table, the SQL statement is:
SELECT * FROM loans
In many cases, we will want to display only selected fields for records that match some condition. To do this we need to use the WHERE keyword followed by a comparison condition involving one of six comparison operators: equals (=), greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), or not equal to (<>), plus a fieldname and a value (see Acess Table
2-1
in Access Tutorial
2
). The general form is:
SELECT FieldName1,FieldName2, … FROM TableName WHERE Query Condition
To find all loan types that have a loan minimum greater than or equal to $10,000, the SQL statement is:
SELECT loanType, loanRate, loanMin FROM loans WHERE loanMin >=10000
5.
Create and run this SQL query.
The results should appear as shown in Access Figure
4-5
.
Access Figure 4-5
Results for loanMin >= 10000.
Whenever you use the equals sign in a SELECT query, you are looking for an exact match. When working with character data, there is a very good way to look for something other than an exact match: using the LIKE operator. The LIKE operator uses the wildcard character as a replacement for unknown or nonexisting characters in attempting to find matches to a group of characters (commonly referred to as a character string). The wildcard character is either the asterisk (*) in MS Access or the percent sign (%) in other database management systems. The general form of this type of query is:
SELECT FieldName1, Fieldname2,… FROM TableName WHERE FieldName LIKE “*value*”
For example, the query to find the same fields as the previous example for automobile loans would be:
SELECT loanType, loanRate, loanMin FROM loans WHERE loanType LIKE “Auto*”
6.
Create and run this SQL query.
The results should appear as shown in Access Figure
4-6
.
|
Access Figure 4-6 Results for LIKE SQL query. |
Using Aggregate Functions in SQL
Access 2007 Animated Tutorials: Aggregate Queries
One operation that is easier to use with SQL than with QBE is to compute certain values about the records in the table using five different aggregate functions—COUNT, AVG, SUM, MIN, and MAX, where their purpose is indicated by their name. In each case, you must use a dummy name for the result of the computation. The form of the calculations is similar among AVG, SUM, MIN, and MAX, whereas that for the COUNT function is somewhat different. The form for the AVG function would be:
SELECT AVG(fieldname) AS DummyName FROM TableName WHERE Query condition
In this SQL statement, the average value is stored in the DummyName variable, which is displayed when the query is run. For example, to find the average interest rate for all items in the tblLoan table, the query would be (the WHERE portion is not needed since all records are averaged):
SELECT AVG(loanRate) AS AvgRate FROM loans
|
7. Create and run this SQL query.
Access Figure 4-7 Result of average interest rate calculation. Using the SUM, MAX, and MIN functions would have the same form as the AVG function. On the other hand, to use the COUNT function, the form is different, as follows: 8. Create and run this SQL query. |
|
Access Figure 4-8 Counting items with interest rates more than 8 percent. |
Inserting or Deleting Records
As we have discussed earlier, you can use MS Access or another database management package to add records to or delete records from a database table. It is also possible to carry out both of these operations using SQL. To insert a record into a table, you would use a statement of the form:
INSERT INTO TableName(field1, field2, … ) VALUES (value1, value2, … )
where the values must be entered in the exact order as the fields in the record separated by commas. Not all fields must be included, but there must a value for any field name that is included.
Fact: If a record with an AutoNumber field is being inserted, the field name for the AutoNumber field must not be included in the list of fields, and no value should be included for it in the Values list.
For example, to insert a new record for a loan with a loan type of “Auto Loan 72 Months,” an interest rate of 11.5 percent, and a minimum loan amount of $20,000 into the loans table, the SQL statement would be:
INSERT INTO loans (loanType, loanRate, loanMin) VALUES (‘Auto Loan 72 Months’, 0.115, 20000)
Note that the loanID field is not mentioned in the loans list because it is an AutoNumber field.
On the other hand, to delete an existing record from a database table, you would use an SQL statement of the form:
DELETE FROM TableName WHERE FieldName = value
We could also use the LIKE operator if the value is a text string to avoid problems with finding exact matches of case or spacing. In either case, this will delete all records that match the criteria. For example, assume that the bank no longer wishes to offer the 36-month automobile loan and needs to remove it from its database. To do this, the SQL command would be:
DELETE FROM loans WHERE loanType LIKE “*36 month*”
|
1. Let’s add and delete records to the loans table using SQL.
Access Figure 4-9 loans table before adding and deleting records. 2. Create an SQL query to insert the new 72-month auto loan record as described previously.
Access Figure 4-10 Running the INSERT query. 3. Since we are making a change to the database, Access will check to verify that we want to make the change.
Access Figure 4-11 Access verification message for the INSERT query. Click Yes to actually insert the new record. 4. Create an SQL query to delete the old 36-month auto loan record as described previously.
Access Figure 4-12 Access verification message for the DELETE query. After reviewing the message, click Yes to actually delete the record from the table. 5. The loans table should contain the records shown in Access Figure
Access Figure 4-13 loans table after insertion and deletion. To change values in a row of a database, you can use the UPDATE and SET keywords in the form: |
Using SQL with Multiple Tables
So far in our discussion of relational databases, we have used a single table to demonstrate the use of SQL. However, the power of a relational database comes from the use of multiple tables that are related by a common field and in which different types of data are stored. We can use SQL to query multiple tables to determine information of interest in a manner similar to querying a single-table database.
The Join Operation
Access 2007 Animated Tutorials: Default Inner Join
The most common operation on a two-table database is the join operation, in which we create a single table from two (or more) tables. Once you understand the join operation, you can then use it to perform calculations and carry out grouping of products. The simplest join operation is creating one table from two. For our example, the SQL instruction to join the loans and customerLoans tables showing all fields in both tables is:
SELECT * FROM loans, customerLoans WHERE loans.loanID = customerLoans.FKloanID
In looking at this query, you can see that even though the field name for the loanID foreign key in customerLoans (FKloanID) is already different from the primary key field name in loans (loanID), we further distinguish between them by combining the table name and the field name using a period, that is, loans.loanID and customerLoans.FKloanID. While this is more for the human users of Access than for the database management system, it is still good form. If this query is run on the database, the result is as shown in Access Figure
4-14
. In this example, the two tables, loans and customerloans, are joined by virtue of the fact that all records from each table that share the same loan ID are displayed in the resulting view.
|
Access Figure 4-14 Result of two-table join operation. |
As another example of the join operation, consider again the query we ran in Tutorial
3
; that is, to display the first and last names of customers from the customers table and the amount outstanding field from the customerLoans table (custLoanAmount) for loans of more than $30,000. In SQL the query would be:
SELECT custLastName, custFirstName, custLoanAmount FROM customers,
customerLoans WHERE customers.custID = customerLoans.FKcustID AND
customerLoans.custLoanAmount > 30000
Note that we have used the AND operator to combine the join and comparison operations in this query.
The result of running this query is shown in Access Figure
4-15
.
|
Access Figure 4-15 Two-table join with condition. |
As we did in Tutorial
3
with QBE, it is possible with SQL to create a three-table join. To display the loan type and interest rate on the loans over $30,000, the SQL statement is an extension of the one shown above:
SELECT custLastName, custFirstName, custLoanAmount, loanType, loanRate
FROM customers, customerLoans, loans WHERE customers.custID =
customerLoans.FKcustID AND customerLoans.FKloanID = loans.loanID AND
customerLoans.custLoanAmount > 30000
As before, running this query will result in the same output as shown in Tutorial
3
, shown here as Access Figure
4-16
.
|
Access Figure 4-16 Three-table join. |
Carrying Out Calculations with SQL
It is also possible to carry out calculations and display them as the result of a join query. For example, we can modify the query associated with Access Figure
4-16
to calculate the amount of simple interest due on each loan this year by multiplying the interest rate times the loan amount. The previous SQL query can be modified as follows to output the first and last names of the customer, loan amount, loan type, interest rate, and interest due:
SELECT custLastName, custFirstName, custLoanAmount, loanType, loanRate,
loanRate * custLoanAmount AS InterestDue FROM customers, customerLoans,
loans WHERE customers.custID = customerLoans.FKcustID AND customerLoans.FKloanID =
loans.loanID AND customerLoans.custLoanAmount > 30000
If we run this query, the result is as shown in Access Figure
4-17
. Note in this figure that the result is the same as was shown in Access Figure
3-17
in Tutorial
3
with the addition of a new field, InterestDue, that has been created as the product of the interest rate and the loan amount. For example, Jesse Mullins owes $15,400 on his loan amount of $280,000 at 5.5 percent interest.
|
Access Figure 4-17 Inclusion of calculated field. |
|
Exercises to Build Your Knowledge of MS Access Match each of the Access terms with its description. 1. _________ COUNT 2. _________ DELETE 3. _________ FROM 4. _________ INSERT 5. _________ JOIN 6. _________ ORDER BY 7. _________ SELECT 8. _________ SQL 9. _________ UPDATE 10. _________ WHERE
(a) An SQL keyword that is used to retrieve records from a relational database. (b) An SQL keyword that is used to set a condition on the records to retrieve or modify from a relational database. (c) An SQL keyword that is used to add records to a table in a relational database. (d) An SQL keyword that is used to change values in a relational database. (e) An SQL keyword that is used to remove records from a relational database. (f) An SQL keyword that is used to sort the retrieved records from a relational database. (g) An SQL keyword that is used to combine records from multiple tables from a relational database. (h) An SQL keyword that is used to specify the tables from which records will be retrieved. (i) A standard language that can be used to work with the data in a relational database. (j) A function that can be used in an SQL query to obtain the number of records that satisfy a query. Follow the directions in this Access tutorial to use SQL to write and execute all of the queries to the loans table in the bank database example database discussed in the tutorial. In addition, you should create and run queries to display the following information: 11. List all the loans. 12. How many automobile loan types are there? 13. What loan type has the lowest minimum loan amount? 14. Which loans have a loan minimum between $5,000 and $10,000? 15. Which loans have an interest rate less than 5% and a loan minimum greater than $10,000? Follow the directions in this Access tutorial to use SQL to write and execute all of the queries to multiple tables in the bank database example database discussed in the tutorial. In addition, you should create and run queries to display the following information: 16. List all customers with a loan having an interest rate greater than 6%. 17. List the e-mail address and birth date of all customers with a loan amount greater than $50,000. |
|
Access Tutorial 4: Mini-Case 1
Scenario: File: Your Task: · Who are the students listed in the database? (Show all fields in student table; save this query as studentList.) · What is the major of Alexis Allen? (Save this query as alexis.) · Which students are over 30? (Show name and age; save this query as over30.) · What are the names, ages, and majors who for those students who enrolled in March 2007? (Save this query as march2007.) · Which students are journalism majors who enrolled in March 2000? (Save this query as marchJour.) · How many students were born on a Monday? (Save your query as monday.) · What is the average age of students who are majoring in computer science? (Save your query as avgCSCI.) |
|
Access Tutorial 4: Mini-Case 2
Scenario: Starter File: Tutorial 4: Mini-Case Link-Saver File: Your Task: · List the usernames and passwords. (Show all fields in user table; save this query as userList.) · What are the e-mails of all users who have entered a link? (Save this query as activeUsers.) · What are the URLs for all links that are in the Search category? (Do not show duplicates. Save this query as searchURLs.) · What links are available for .com sites? (Include title, link, and category. Save this query as dotComs.) · Write the SQL command to add a new link to the links table. (Save this query as addLink.) · Write the SQL command to add a new user to the user table. (Save your query as newUser.) · Write the SQL command to delete all records in the userLinks table related to user 1. (Save your query as linkDelete.) |
Appendix: Introduction to Data Modeling
|
After reading this appendix to data modeling and completing the associated exercises, you will be able to: · Discuss the use of logical modeling for database design. · Describe an ERD and create one. · Understand data models and create one. |
The Importance of Relational Databases
As a relational database application, MS Access can work with more than one database table. As discussed in the Access Tutorials, the relational model enables Access to store data in tables and then link tables via common fields. You may ask why it is necessary to do this. Why not just use one table with all of the information in it? To help you understand the reasons for using multiple tables, consider a revised version of the customer table from the Access Tutorials, shown here in Appendix Figure 1. Note that we have included all of the information about each customer plus information about the customer’s loans.
|
Appendix Figure 1 Single table with all data. |
In looking closely at Appendix Figure 1, you can probably see a big reason for not using a single table: redundancy. Note that the Customer table now lists each customer’s ID, first and last names, birth date, e-mail address, loan origination date, and loan amount, status, type, and interest rate. For customers with multiple loans, we have repeated all of the information about them for each loan.
Note that this redundancy not only can result in the database table taking up unneeded storage space (especially for a realistic-sized database table involving millions of records) but also causes problems when trying to insert new records, delete existing records, or update records. These problems, typically referred to as anomalies, can harm the integrity of the database records.
Therefore, to solve the problems associated with storing all the data in one table, relational databases are used. Relational databases store data in multiple tables in which records in one table can be related to records of another table. To create the appropriate tables, most knowledge workers rely on logical modeling. Logical modeling provides tools to help analyze and understand what data items are important and the relationships between the data.
|
Thinking Critically: Relational database proponents often highlight the reduction in data redundancy inherent in relational databases. What redundancy does the relational model add? Think about why a relational database can store data in discrete but related tables. How could data redundancy increase lack of data integrity? |
Logical Modeling: Entity-Relationship Diagrams (ERDs) and Data Models
Businesses generate important data as a result of their day-to-day operations. In order to store and use this data, databases are created and updated as necessary. Prior to creating a database it is important to understand what data your business needs. Logical modeling provides tools to help businesses analyze and understand what data items are important and the relationships between the data. Logical modeling creates a picture of this world of data and relationships. An accurate logical model provides a business with a solid foundation upon which to build its database(s).
In logical modeling terms, entities are important things, such as customers, products, or students, about which a business or an organization wants to capture and store data. Entities have attributes, or characteristics, which are also important and serve to organize the data stored about an entity. For example, most databases have customer ID numbers or social security numbers (SSNs) that identify customers.
|
Thinking Critically: Would your phone number be a good unique identifier (primary key) for you? How about your birthday? Can you think of a good primary key (unique identifier) other than SSN? |
Let’s try an example that involves reading a business scenario, picking out the entities and attributes, and creating the entity-relationship diagram (ERD) and the data model. Some people use only one or the other of these models, but they work well together, too. The ERD is uncluttered by attributes, so you can focus on the entities and the relationships. The data model adds the attributes and helps to organize them before you actually create the database. Primary and foreign keys are identified as well.
The data model is built after the ERD by adjusting some symbols and adding more detail. It becomes your blueprint for building your database using software such as Access. Using both an ERD and a data model allows knowledge workers to focus first on what’s important in the real-world scenario—entities and relationships—and then, by transforming the ERD into a data model, to organize the data to conform to the rules of relational databases.
Scenario 1: Kraig’s Kayak Shoppe (note the spelling of “shoppe”; these are fancy kayaks!) sells kayaks and related products. He has a very limited inventory, so he limits his customers to one kayak/related product per customer. (OK, I’m reaching on this one, but bear with me.) He wants to keep track of each customer and the product the customer bought.
Access 2007 Animated Tutorials: Many to Many Relationships
Determine each “important thing” about which Kraig needs to capture and store data. Recall what the database term for an “important thing” is: entity.
Customer is a good start, and so is Product. Notice how these entities are all nouns. Noun-hunting is a good way to spot entities, but you may bag a few attributes, too, so you have to be careful.
|
Thinking Critically: Is “Kayak” a good choice for an entity? |
Using standard symbols, draw the entities and the relationships between the entities. The standard symbols for an ERD are shown in Appendix Figure 2. In the figure, we see that entities are represented by box shapes in the ERD and the relationships are represented by lines with a diamond shape in the middle. Relationship symbols are labeled to indicate the cardinality/ordinality of the relationship. In other words, labels indicate the number of records that can be related between tables. The relationship symbols shown here represent the three possible relationships between data: one-to-one, one-to-many, and many-to-many, respectively.
|
Appendix Figure 2 Standard ERD symbols. |
Once an ERD is drawn, you can actually “read” the ERD to understand the relationships between entities. For example, in the ERD snippet of Appendix Figure 3, reading from left to right, “a customer buys one-to-many products.” Note that the 1:M is next to the Product entity. Note also that we have added the verb in the sentence, “Buys,” as an additional label to the diagram.
|
Appendix Figure 3 One-to-many relationship. |
Reading from right to left, “a product is bought by ‘one instance of a customer’ or ‘one and only one customer.’” Note that the 1:1 is next to the Customer entity and that we have once again labeled the diagram so this relationship is easy to read.
Now consider this more realistic, revised scenario 2: Kraig’s Kayak Shop (note the spelling of “shop”; the fancy kayaks are gone!) sells kayaks and related products. His inventory is extensive, so customers can buy as many of each product as they need or want. Kraig wants to keep track of each customer and the products that the customer purchases.
Reading from left to right in Appendix Figure 4, “a customer buys one-to-many products.” Note that the 1:M is next to the Product entity.
|
Appendix Figure 4 Many-to-many relationship. |
Reading from right to left, “a Product is bought by one-to-many customers.” Note that this time the 1:M is next to the Customer entity. (So, each time you make a sentence to describe the relationship from one entity to another, it is standard convention to place the label next to the second entity in the sentence.)
The revised ERD includes what is known as a many-to-many relationship between the Customer and Product entities, because many customers buy many products and many products are bought by many customers. You must resolve (change) these relationships, because many-to-many relationships violate the rules upon which your relational database will be built.
Typically, a many-to-many relationship is drawn as a new relational entity (note the dashed box around the diamond in Appendix Figure 5) that lies between the original two entities. From the standpoint of the original entities, the relational entity is connected to each original entity by a one-to-many relationship. You can say that the many-to-many relationship (Appendix Figure 5) has been transformed into two one-to-many relationships and a relational entity (Appendix Figure 6).
THIS …
|
Appendix Figure 5 Many-to-many relationship (before transformation). |
… BECOMES THIS
|
Appendix Figure 6 Two one-to-many relationships with relational entity (after transformation). |
|
Thinking Critically: How would you read these relationships, from left to right and from right to left? What does this tell us? |
Now we’ll show you the Data Model for the ERD shown in the first scenario (Appendix Figure 7).
|
Appendix Figure 7 One-to-many relationship in data model (Scenario 1). |
Reading from left to right, “a customer buys one to many products,” and reading from right to left, “a product is bought by ‘one instance of a customer’ or ‘one and only one customer.’” Notice how the symbols changed when the ERD was converted to a data model. The relationship symbol has changed from a “ labeled diamond” to a “line with crow’s foot.” The crow’s foot is placed on the “many side” of the relationship. We have also added more detail to the entities. This additional detail includes a listing of the attributes (fields) for the entities and indicators for the primary and foreign keys.
So why are there asterisks in front of CustomerID and in front of ProductID? This indicates that these attributes are the unique identifiers for their respective entity. CustomerID uniquely identifies each instance of a customer, and ProductID uniquely identifies each instance of a product. A more common term for unique identifier that you have already seen is primary key. We will use the two terms interchangeably to remind us that, ultimately, we will create a physical database from this logical model. Primary keys are fields in the tables in a relational database. The physical database uses primary keys to identify each row in each table uniquely.
If an asterisk indicates a primary key, then what is FKCustomerID and why is it in the PRODUCT entity? FK stands for foreign key, and it designates the attribute from the “one” end of the relationship that is duplicated and placed with the attributes of the entity that serves as the “many” end of the relationship. In the physical database, using the primary key from the table at the “one” end as a foreign key in the table at the “many” end will enable us to link the two entities and establish a relationship when we create our database.
Creating a Data Model for the Bank Example
Let’s think about the bank example that is used in our Access tutorials. In that example, we have a Loan entity that contains information on the various types of loans available from the bank. We also have a Customer entity that contains information about the customers of the bank. Many customers can take out many different types of loans. In terms of an ERD, we have the situation shown in Appendix Figure 8, with a many-to-many relationship between the Customer entity and the Loan entity.
|
Appendix Figure 8 Many-to-many relationship between Customer and Loan entities. |
We now convert this ERD from an m:m relationship into two 1:m relationships as was done earlier, only this time using the CustomerLoan entity, we now have the ERD shown in Appendix Figure 9.
|
Appendix Figure 9 Conversion of ERD to two 1:m relationships. |
The next step is to convert this into a data model using the three entities: Customer, CustomerLoan, and Loan. If this is done, the result is shown in Appendix Figure 10.
|
Appendix Figure 10 Data model for bank example. |
This is the same data model we used in Access, only there it is shown in a different fashion. Compare Appendix Figure 10 with the copy of the relationships from Access shown here in Appendix Figure 11. Note that instead of the crow’s foot symbol used to show the “many” side of the relationship in the data model, Access uses a key symbol to identify the primary key of each table and the infinity symbol to indicate “many” relationships. Other than that, they are very similar.
|
Appendix Figure 11 Relationships in Access. |
|
Exercises to Build Your Knowledge of Data Modeling
Assume that a certain state in the United States has two state parks: Heart State Park and Diamond State Park. Appendix Table 1 shows the two state parks and any hiking trails that are in them or go through them along with the trail length in that park. (Trails may go outside the state parks as well.) The state division of natural resources wants to create a database for the hiking trails that go through its state parks that will include such information as park name, county, address, and so on as well as trail name and trail length. Appendix Table 1 State parks and hiking trails Heart State Park Diamond State Park Star (5 miles) Arrow (8 miles) Arrow (10 miles) Boomerang (4 miles) Boomerang (3 miles)
Your assignment is to create an ERD for this situation. Then using the ERD you have created, create a data model. If you are feeling ambitious, create a database that is based on your data model. |
|
Data Modeling Tutorial Mini-Case
Scenario: Your Task: |
Microsoft Access Project 1: The WildOutfitters “How-To” Courses Database
|
By reading the business case sections and completing the three parts of this project (Parts A, B, and C) you will: · Extend your knowledge of Microsoft (MS) Access tables, forms, queries, and reports. · Understand the relationship between the logical model of a database and the physical implementation of that database. · Understand how to implement referential integrity and discover its purpose. · Learn how to apply Access to solve business problems. Files for Download: Files associated with this project for you to download are the following: · The Mini-Case data model file, ACC_Project_WildOutfitters_Data_Model_1.pptx Starter File: Project 1: Wild Outfitters |
Project Introduction
Data and databases are at the heart of many business information systems. If you think about the daily interactions you have with organizations, you will discover that many of these interactions generate data that organizations use to create value for you and for them. For example, when you shop at a retail grocery store, data is gathered about your purchases and your method of payment. If you choose to pay with a debit or credit card, then additional data may be gathered about you and your purchase. Many stores offer a discount if you use some type of “customer” card. This enables the store to gather data about your specific purchases and to provide value to you through coupons and other sales promotions. It also enables store management to make better decisions about product selection, pricing, promotions, and so forth. This project will focus on how a retail firm might achieve its goals of generating new business and increasing customer satisfaction by providing a knowledge-oriented service to its customers. You will create the database that will allow this company to accomplish these goals.
General Business Case
Wild Outfitters, Inc. is a well established national outdoor-sports retail products chain with a local store in your area. It carries a complete line of name brand and locally produced equipment and supplies for outdoor enthusiasts. Through customer surveys, Wild Outfitters discovered that many of its customers are beginners and buy equipment but do not know how to use it properly. Rather than view this as a problem, Wild Outfitters sees this as an opportunity to provide better service to its customers and to gain a business advantage over its competitors. Wild Outfitters is going to offer “how to” courses for its customers. During these courses, customers will learn how to use the equipment they purchased, but they will also learn about other equipment they need and might want to purchase. Wild Outfitters will use this free (to the customers) service to increase sales and customer satisfaction. Isaac Timberlake, a student at your business school, is an intern at Wild Outfitters, and Sara Johns, the store manager, asks Isaac to create a Course Management System that she can use to track the courses customers take and the employees who teach the courses. Isaac understands the business requirements, but he needs your help in designing the database that will be at the heart of the Course Management System. The end result of your collaboration with Isaac will be the relational database and associated features specified in the MS Access Project instructions that follow.
Project Data Model Instructions
You will need to download the PowerPoint file ACC_Project_WildOutfitters_Data_Model.ppt. Your instructor will tell you how to do this. This file contains the data model you will reference in order to create your database. There are several slides in the presentation. The first slide is the data model, and the remaining slides are examples of how your tables might look. Remember, you will have to use MS Access to create the actual tables, and you will have to add additional data to meet project requirements.
Part A: Creating the Tables
Part A Problem Definition
Isaac Timberlake welcomed the challenge of creating an information system that will help create business value for Wild Outfitters, Inc. This project focuses on the database component of that IS, and accomplishing this project will enable you to help Isaac by applying and refining the IS skills you’ve learned. Before you can build the database, you will have to understand the data that must be captured by the system and sketch out how this data will be stored. You should try this on your own and then compare your model to the one found in the ACC_Project_WildOutfitters_Data_Model.pptx file.
Part A Problem Requirements
Remember, tables are the objects that MS Access uses to store data. Your first task in this part of the project is to help Isaac by using Microsoft Access to create the required tables (see the ACC_Project_WildOutfitters_Data_Model.pptx file). You should use input masks for fields where appropriate (e.g., zip code). If you do not know what an input mask is, use MS Access’s Help function to find out. Next, you will need to populate the tables with data. You should make up the data yourself, following the examples in the ACC_Project_WildOutfitters_Data_Model.pptx file. You should create at least 10 records (rows) in the customer table and five records each in the employee, class, and course tables. Your instructor may require that you add more data. You may want to accomplish Part B of the project before you enter all of your data. Lastly, you will need to create the appropriate relationships between the tables. When creating relationships, you should ensure that referential integrity is enforced.
|
Part A Interpretive Questions Based on your work in Part A, answer the following questions: 1. What is the relationship between an instance of a primary key and a record (row) in a table? 2. What is referential integrity and why is it important for Isaac (and you) to enforce referential integrity in the database you create? |
Part B: Inputting Data into the Database and Creating Forms
Part B Problem Definition
Isaac discovered that, rather than entering data directly into the tables, he could use the MS Access database objects known as forms to enter data into the database. He used the forms that you created to make data entry more efficient and less error-prone.
Part B Problem Requirements
Use the Form Wizard to create a form that will allow you to input data into each of the tables. Although the forms that you create are useful tools for entering data, when a customer makes a purchase, it would be useful to have the option to sign the customer up for a class.
|
Part B Interpretive Questions Based on your work in Part B, answer the following questions: 1. In Part A of the project, you may have entered data directly into the tables. Although this is convenient, why are forms a better way to do this? Why might it be a good idea to avoid entering data directly into the tables? 2. Forms work well, but given additional hardware and software resources, how could you improve the data entry process to reduce errors? |
Part C: Creating Different Views of Data for Decisions; Creating Database Queries
Part C Problem Definition
Ms. Johns asked Isaac to find out which employees are leading which courses and classes and when they are leading them. She also wants to know which customers are taking classes and when. Lastly, she wants to be able to view the data for each instructor and for each month. Although the data stored in your database is organized into tables, looking at all of the tables to answer her questions or to help her make decisions is inefficient. Fortunately, MS Access provides an object—the query—and a query tool—query by example (QBE)—which makes it easy for you to view selected subsets of your data. In other words, Access makes it easy for you to ask and answer questions about your data and then use this information to make business decisions.
For the purposes of this project, you can think of a query as a question you ask of your database. In addition to QBE, the visually oriented tool you will use here, Access also provides another tool to create queries, although this tool is actually a computer language. Structured Query Language (SQL) is a standardized language for querying databases. With it you can create complex queries of almost any database; however, using SQL is beyond the scope of your project requirements (see Part C Interpretive Question 2).
Part C Problem Requirements
Using the Design view and QBE, create the following queries. You may need to add more data to your database so that you can test your query results.
|
Query 1: What customers have signed up to take classes from which instructors? (This query should return the data in your database for all courses, classes, customers taking classes, and employees leading classes.) Query 2: Who is taking and leading a course in September? (This query should return the courses, classes, customers taking classes, and employees leading classes for September only.) Query 3: What classes is Sara Johns teaching in September? (This query should return the courses and classes that Sara Johns is leading in September only.) Query 4: What classes are being lead by either Sara Johns or Isaac Timberlake in September? (This query should return the all of the courses and classes that Sara Johns and Isaac Timberlake are leading in September only.) |
|
Part C Interpretive Questions Based on your work in Part C, answer the following questions: 1. When querying a database using QBE, what are criteria used for? 2. When using QBE, what is the difference between asking, “What are all the classes that Employee A and Employee B are teaching?” and “What are all the classes that both Employee A and Employee B are teaching together?” 3. Although we mentioned that SQL was beyond the scope of this project, you can look at the SQL view for any of your queries. It is one of the choices on the same View menu that you used to select the design view after running your queries. Look at the SQL for Query 2 above. Can you make sense of the SQL query? Look for things like table and field names. You can even change the September query to an August query by changing only one character—try it! |
Part D: Outputting Data to Decision Makers and Other Knowledge Workers; Creating Reports
Part D Problem Definition
Queries are useful tools for asking questions of your data and for transforming data into information that is useful to decision makers. However, the standard query table view is not as useful or as easily readable as most users need or want. Ms. Johns asked Isaac to organize the views of Wild Outfitters data and present it in a well-organized form.
Part D Problem Requirements
To organize the views of Wild Outfitters data and present it in a well-organized form, Isaac will use the MS Access Report object. A report can be based on a table or a query or a combination of tables and queries; however, for Part D of this project you need only to create a report based on Query 2 from Part C above. You can use the Report Wizard to create your report. The report should show all of the data contained in Query 2. When responding to the Report Wizard’s prompts, you should select to view your data by class, add “class date” as grouping level, and sort the detailed records by customer last name in ascending order. The layout and style choices are up to you. Finish the report and name it septemberQuery.
Review your report and note the “database speak” used for the title and headings. Since the purpose of a well-crafted report is to convey useful information effectively and efficiently, you will need to edit your report. Here are the minimum requirements for Part D of the project.
|
1. Change the orientation of the report from portrait to landscape. 2. Change the title of the report to “Wild Outfitters September 2008 Classes.” 3. Move the customer first name label and data so that they are next to each other, last name then first name. 4. Transform all field names into easily readable headings; for example, CourseName should become Course Name, CourseType should become Course Type, and so forth. |
Your instructor may add additional requirements or you may want to explore just how professional you can make the report appear.
|
Part D Interpretive Questions Based on your work in Part D, answer the following questions: 1. What other reports would you suggest that Isaac should create for Ms. Johns to help her understand and manage the courses and classes that Wild Outfitters provides to its customers? 2. We have mentioned several Access objects throughout this project. Did you notice any additional objects when you were working in the Design view? What were some of these objects, and how did you know that you were looking at an object? |
|
Review Progress Below is an indicator of how far you have progressed towards completing this assignment. Resource Name Resource Type Progress Tutorial 1: Introduction to Microsoft Access 2007 Student Resources Viewed Tutorial 2: Queries and Reports in Microsoft Access 2007 Student Resources Viewed Tutorial 3: Working with Tables in Microsoft Access 2007 Student Resources Viewed Tutorial 4: Microsoft Access 2007 and SQL Student Resources Viewed Appendix: Introduction to Data Modeling Student Resources Viewed Microsoft Access Project 1: The WildOutfitters “How-To” Courses Database Student Resources Viewed Aggregate Queries Student Resources Viewed Default Inner Join Student Resources Viewed Field Sizes Numbers Student Resources Viewed Field Sizes Text Student Resources Viewed Indexing Student Resources Viewed Many to Many Relationships Student Resources Viewed Properties Student Resources Viewed Referential Integrity Student Resources Viewed Straight Line Student Resources Viewed Validation Rules Student Resources Viewed Tutorial 2: Bank Database Example Student Resources Viewed Tutorial 2: Mini-Case Employees Student Resources Viewed Tutorial 3: Bank Database Example Student Resources Viewed Tutorial 4: Bank Database Example Student Resources Viewed Tutorial 4: Mini-Case Students Student Resources Viewed Tutorial 4: Mini-Case Link-Saver Student Resources Viewed Project 1: Wild Outfitters Student Resources Viewed
Completion Status: 100% |
Access Assignments Complete List
1. Acc 2 Bank Database
2. Acc 2 MC Employees
3. Acc 4 MC Linksaver
4. Acc 4 MC Students
5. Acc Project Wildoutfitters
