Access Homework Help

Overview

 

 

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

 

You want to develop a database that contains 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

 

 

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 product cost (List Price * Discount
Percentage, subtracted from List Price)

        Orders 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.]

 

You will also, from time to time, want to see different pieces of
information displayed for the records in a table, 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 and Zip Code fields

        Value lists whenever there is a finite number of
options from which the end-user can choose

        Validation Rules to control the values that are being
entered into fields

        Case control for the State field

        Required fields whenever certain information may not
be omitted

 

 

 

 

 

 

 

 

Specifics

 

 

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

 

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
tablâ�¥ç•³æ‘¢ç‘¡ç�¡æ•¨ç‘¥æ„ æ‘®æ¤ â�®ç•±ç‰¥æ•©â¹³à´ à¶ æ½™â�µæ…·ç‘®ç� â�
¯æ•«ç�¥ç� æ…²æ­£æ¼ â�¦æ¡´â�¥æ½¦æ±¬ç�¯æ¹©â�§æ¹©æ½¦æµ²ç‘¡æ½©â�®æ½¦â�²æ…¥æ¡
£æŒ æ¹¯æ…´ç‘£â€ºê€�

0

2

B

а

в

Ñ‚

T

V

f

ш

ÑŠ

�d�d⑛�Ĥ摧ᓈ‘�        Contact ID#

        Contact First Name

        Contact Last Name

        Contact Position

        Contact Phone

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

        The company to which the contact pertains

 

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 8 sales reps, one for each territory.  The sales reps need to
be tied to the customers so that a sales rep may be displayed with
his/her customers in a table subdatasheet and in queries.

 

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

 

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

 

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 of order

        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 sheets 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 it should be separated out for your tables.

 

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).