ACCESS LNC

Week 4 The Final Database Project

 

The following is an overview and specific description of the database that you're going to be designing for the final project. Read through these specs to get an idea of what will be needed and how you need to be planning the tables.

 

As indicated in the "The Data" section, all of the information you'll need for this project is in an Excel file named Final ProjectData.xls. If you haven't already downloaded this file from the shared 90.171.031 folder, you need to do that now.

 

I suggest you print this description out, and keep it handy as you go about planning your database.

 

 Overview

 

You work for a company named World Wide Imports, which has customers throughout the U.S.

 

You want to develop a database that keeps track of the following:

 

· Customer names and addresses

· Contacts for each customer

· Sales reps within your company who handle customer accounts

· Territories within which the sales reps operate

· The orders placed by customers

· A list of the different products your company sells to the different customers

· Prices and descriptions of the products that you sell and that have been sold

 

You ultimately will be doing numerous queries, to extract different pieces of information, including the following:

 

· Customers listed with contacts and the sales reps who handle the account

· Customers listed with the products they have ordered

· The total cost of each product that you sell (calculated by multiplying the List Price by the Discount Percentage, and then subtracting that result from the List Price)

· The orders that have been placed within a range of dates

· Customers within a particular territory who have bought specific products

· A list of customers who are inactive, so that they may be archived

· A list of the completed orders

· The total amount ordered by each customer

 

You plan to send letters to the contacts in all of the companies who order from you, with information about new products. Therefore, you know that you will need all the necessary fields for a form letter mail merge.

 

You want subdatasheets in your tables that will display the following:

 

· Customers with contacts

· Sales reps with customers and contacts within each company

· Products with the orders that have been placed for that product

· Territories with the customers in that territory, then with the contacts for each customer. [Each customer will potentially have multiple contacts – President, Vice President, CEO, Marketing Manager, etc.]

 

In the subdatasheet for a given table you will also, from time to time, want to see different pieces of information displayed for the records, and will therefore need to change the contents of a subdatasheet.

 

In order to make the data-entry process easier, you will create the following:

 

· Input masks for Phone fields

· Value lists whenever there is a finite number of options from which the end-user can (or should) choose

· Validation Rules to control the data that are being entered into fields

· Case control for the State field so that it's always displayed in uppercase regardless of how the data are entered

· Required fields whenever certain information should not be omitted

 

 

 

Specifics

 

 

1. There are 20 customers with whom you work. You want to keep track of the following information for each customer:

 

· Customer ID#

· Customer Name

· Customer Address

· Customer City

· Customer State

· Customer ZIP

· Customer Territory #

· The ID# of the Sales Rep who handles the account

· Whether the customer is active or inactive

 

2. There are 35 contacts for the different companies. Some customers have one contact, while others have as many as three. The contacts need to be tied to the company, so that you will be able to display the contacts for each company in a table subdatasheet and in queries.

 

3. You want to keep track of the following information for each contact:

 

· Contact ID#

· Contact First Name

· Contact Last Name

· Contact Position

· Contact Phone

· Contact Title (Mr., Ms., etc.)

· The company to which the contact pertains

 

4. There are 8 territories in which the sales reps operate. Each territory has a code number (1 – 8) and a full name.

 

· The territories will need to be tied to the sales rep, so that a given territory may be displayed with its sales rep in a table subdatasheet and in queries.

 

· There are currently 8 sales reps, one for each territory. The sales reps need to be tied to the customers that they handle, so that a sales rep may be displayed with his/her customers in a table subdatasheet and in queries.

 

· Currently each territory only has one rep, but it's conceivable that others will be added in the future for a given customer, so you need to accommodate the potential for expansion.

 

5. You want to keep track of the following information for each sales rep:

 

· ID #

· Last Name

· First Name

· Territory in which they operate

· Phone

· The number of hours they work

· The date they were hired

 

6. You need to maintain a list of the 49 products you sell. You want to keep track of the following information for the products:

 

· Product Code

· Description of the product

· Category

· Size

· Finish

· List Price

· Discount Percent

 

7. You also need to keep track of the orders placed by customers, including the following information:

 

· Product code

· ID# of the customer who purchased the product

· ID# of the sales rep who handles the account

· Quantity ordered

· Date the order was placed

· Date the order was filled

· Whether or not the order is complete

 

 

 

The Data

 

 

All of the data you need for this project have been created for you, in an Excel workbook file named FINAL PROJECT DATA.XLS. This Excel file is one of the files you have downloaded from the server.

 

There are four worksheets in the workbook file:

 

Customers, Contacts & Reps

Territories

Products

Orders

 

But be careful:  The layout of the sheets of data in the Excel file is not necessarily the organization that will work for your tables. In fact, you can bet it isn't.  (Things don't work that easily when we're designing databases). You'll need to look at the data carefully and decide how they should be separated out for your tables. Spend some time on this.

 

As you begin this project, your first step (and this is a critical step) is to decide how your tables are going to be organized. Specifically:

 

         Which fields will go in which table?

 

         What will be the primary key field in each table?

 

         What will be the foreign key field in each table?

 

         How will each table be related to the other tables in the database, and what type of relationship will it be?

 

Please take the time to plan this out, based on what you have to work with (that is, the data in the Excel file).