CS 107 Computers and Their Applications

Fall 2006

Buckman Hall 120

Team Assignment 6 - Creating a Customer and Transaction Database

Course Home Page

Syllabus

Course Schedule

Team Final Project
Your business moving along just great and your customer list is expanding. You and your partners decide it's time to organize your customers into a database so you can quickly pull up information about your customers as you need to. You also decide to keep track of your business transactions in the database, so you can create invoices or statements for your customers. Since you purchased Microsoft Office, you decide that you will use Microsoft Access for your database. After much thought, you and your partners decide that you need to create at least three tables and two queries as a start for you business database. A description of each table and query is listed below.
Some business may wish to go even further with Access and create a form and/or a report to better serve their business's needs.
The minimum database requirements are due by November 17, 2006

Table One

This table will have your customer's information. You should develop a customer ID of some type to uniquely identify each customer. You must also use another table as a look-up table. A suggestion is the example that was given in class of using a table of state codes for the customer's address. The minimum information you need for this table, besides the customer ID #, is the customer's name, address and phone number. Be sure to break the address down into all the possible fields. You may need more fields for your table depending upon the information your business needs to keep about a customer. This table should have a minimum of 20 records.

Sample - Customer table

CustomerID	CustomerLastName	CustomerFirstName	StreetAddress	City	StateCode	...
SJ100	Smith	John	3 South Main Street	Milford	CT	...

Table Two

This table should contain the information that you will use for your look-up table for your customer table. It does NOT have to be the state abbreviation if you have some other suitable field which requires verification, but you may use the state code if you have nothing else appropriate. This table should have at least 10 records.

Sample - Look-up Table

StateCode	State
CT	Connecticut
VT	Vermont

Table Three

This table should contain the information your business generates for each customer transaction. The fields you need in this table will depend upon the type of business you have. The purpose of this table is to allow your business to create a customer invoice or a customer statement. This table should use an ID # for each transaction, the customer who caused this transaction ID #, and the fields need to record a transaction for your business. This table should have at least 10 records.

Sample - Customer Transaction Table 1

OrderNo	CustomerID	ProductNo	ProductName	QuantityOrdered	UnitPrice	BackOrdered	...
100034	SJ100	A-04	A1 Widget	200	1.99	No	...

Sample - Customer Transaction Table 2

CaseNo	CustomerID	WeekOf	HoursBilled	Provider	AdditionalCosts	...
D0056	SJ100	10/30/06	14	E. T. Hammer	20.00	...

Query One

The purpose of this query is to produce a list of your customer's address information, listed in alphabetic order first by state, then by the customer's last name. This query should NOT include the StateCode, but should include the FULL state name. It will be necessary for you to include both the customer's information table and the State code table in this query.

If you are using another piece of information for look up in your customer database, you will have to use your look-up table, and not the State code table in this query.

Query Two

The purpose of the second query is to extract the information from your tables that you will need to create a customer invoice or customer statement. This query should join the customer information able to the transaction table to get the information needed to produce your business's invoice/statement. Remember, you will need the customer's name and address as well as the transaction information. The information in this report should be listed in order by transaction ID.

Extra Credit

For those wishing to get extra credit on test #2, you should use Access to produce either a customized form which you could use to enter a new customer's information or a report of the customer's statement/invoice. The form should include the information you have listed in your look-up table and all of the fields you need filled out for each customer. If you do not want to make a form, you may create a customized report based on the second query in this project. This report should list all of the transaction information and customer information you obtain in the second query. Each invoice/statement should be listed on it's own page so that you can send them to your customers.

Which ever one of the extra credit options you may chose, your form or report must include your business logo (WordArt), business name, and business address.

Due Date: November 17, 2006

Assignment Requirements:

All information collected into a neat packet with a cover sheet containing business name and all team members
A copy of all tables in design view
A copy of all tables in data view
A copy queries in design view
A copy of the results of all queries
Extra Credit: A copy of either the form or the report in design view
Extra Credit: A copy of either the form or the report in data views

Due Date: November 17, 2006

Last Updated: 10/30/06