CST 134: MS Access Assignment
Problem Overview:
/ ExtremeDescentSnowboardsScenario: You have been asked to create an application to support the sales and marketing functions of a company that manufactures and sells snowboards.
As part of that application, you will create a Microsoft Access database that will support recording sales transactions.
As a proof of concept, you will create some sample forms and queries that will demonstrate how this application might function as a decision support system for the Sales and Marketing Departments.
Step 1: Create Tables
- Using MS Access, create a product table, a customer table, and an order according to the following specifications. Use the names, datatypes, and descriptions as indicated. (Hint: If you have questions about specific fields, look at the data that is going to be entered into the tables.)
Attribute Name / Data Type / Description
prodno / number / The primary key - used to uniquely identify each product
desc / text / Product name or description
price / number (or currency if supported by the DBMS system) / Current price of the product
Customer Table
Attribute Name / Data Type / Description
custno / number / The primary key- a unique customer number to identify each customer
custname / text / Customer' s name- including first and last name
addr / text / Customer's street address
custcity / text / Customer' s city
custstate / text / Customer' s state or province
custzip / text / Customer' s zip or postal code
country / text / Country name
wphone / text / Work phone number
hphone / text / Home phone number
email / text / Customer' s email address
Order Table (continues on next page)
Attribute Name / Data Type / Description
ordno / number / The primary key - a unique number for each order
custno / number / A foreign key - custno is the primary key in the customer table. It will be used to link the order table with the customer table.
prodno / number / A foreign key - prodno is the primary key in the product table. It will be used to link the order table with the product table
saleamt / number (or currency if supported by the DBMS) / The price of the snowboard purchased by the customer (only one board can be ordered per transaction)
orddate / date / The date the order was placed
saletype / text / The type of sale made: Internet, Phone, or Direct
payment / text / Type of payment. May be credit card, cash, or COD
crdtype / text / Name of the customer' s credit card. May be Passport, MiseryCard, Discovery, American Indebtedness, or Diver' s Club
crdno / text / Card number
expdate / date / Expiration date of credit card
Step 2: Enter Data Into Tables
- After you create the three tables using Access, you will need to enter some test data into these tables. Enter the data, as indicated, for the appropriate table. Pay attention to detail and enter the information as written. Print and label the tables after adding the data.
prodno / desc / price
2101 / Freeform / 395.00
3120 / Halfpipe / 350.00
4290 / Crossbow / 295.00
5435 / Sidecut / 250.00
Data For Customer Table
custno / custname / addr / custcity / custstate / custzip / country / wphone / hphone / email
1 / Gloria Simon / 404 Prairie Rd / Sycamore / IL / 60178 / US / 815-123-4567 /
2 / Roger Gibbons / 109 Hagen Dr / Burbank / CA / 91501 / US / 818-457-8989 / 818-444-9878 /
3 / Walter Stein / 494 Crane Cir / Boise / ID / 83703 / US / 208-145-9898 /
4 / Jennie Henderson / 9003 Lincoln Hwy / Portsmouth / NH / 00215 / US / 603-898-7412 / 603-558-9686 /
5 / Satya Patel / 124 Timber Ln / Denver / CO / 80201 / US / 303-589-9865 / 303-589-5554 /
6 / Susan Porter / 33 Kimberly Dr / Stonington / ME / 04681 / US / 207-874-2356 /
7 / Alvin Poe / 981 Ridge Rd / Cheyenne / WY / 82001 / US / 307-986-5879 /
8 / Tammy King / 4 Spring St / Madison / WI / 53701 / US / 608-154-5698 / 608-541-9999 /
9 / Sun Kim / 99 Charles Pl / DeKalb / IL / 60115 / US / 815-554-5568 /
10 / Peter Hall / 199 16th St / Denver / CO / 80201 / US / 303-558-9865 / 303-885-8655 /
Data For Order Table
ordno / custno / prodno / saleamt / orddate / saletype / payment / crdtype / crdno / expdate
1 / 1 / 2101 / 395 / 12/08/98 / internet / card / Passport / 154-45-78 / 12/31/2001
2 / 3 / 3120 / 350 / 12/05/98 / phone / COD
3 / 2 / 2101 / 395 / 12/09/98 / internet / card / Passport / 225-56-74 / 09/30/2005
4 / 5 / 4290 / 295 / 12/12/98 / direct / cash
5 / 4 / 3120 / 350 / 12/15/98 / direct / card / MiseryCard / 565-65-98 / 07/31/2002
6 / 6 / 2101 / 395 / 12/15/98 / phone / card / American Indeptedness / 555-55-41 / 12/31/1999
7 / 8 / 5435 / 250 / 12/16/98 / internet / card / Diver's Club / 233-45-87 / 10/31/2001
8 / 7 / 2101 / 395 / 12/17/98 / phone / card / Discovery / 233-65-88 / 09/30/2002
9 / 9 / 4290 / 295 / 12/18/98 / direct / cash
10 / 10 / 3120 / 350 / 12/19/98 / internet / card / Passport / 444-77-77 / 12/31/2001
Step 3: Create Online Forms
- After you have entered the data into the three tables, create three forms for entering and editing the data in the tables. Remember, forms are designed to be used by other users to manipulate a single record at a time, so you will need to make them as intuitive as possible. Print a copy of each form.
Step 4: Create Online Reports
- After you have entered the data into the three tables, create three reports for displaying the table data. Remember, reports are designed to be used and viewed by other users, so you will need to make them informative. Use the report formatting features and be creative. Print a copy of each report.