Comprehensive Access Assignment
The policy on late assignments is stated in the syllabus.
Deliverable: You are to upload to Blackboard the Access Database containing all the work required in this assignment. The database should be named
where you substitute your first name for yourfirstname and your last name for yourlastname.
This is an individual assignment. Any electronic duplication of another person's work-in whole or in part-is expressly prohibited. The Gabelli School Academic Integrity Policy will be invoked in the event of violations.
Important Advice: Read the entire assignment before beginning to work on it.
Our company, Plenum, Incorporated, which is in the data analytics business, has decided to use Access to create a relational database to keep track of our employees and clients. Here are the data we have collected:
|
Employees
|
|
Last Name
|
First Name
|
Employee ID
|
Department
|
Annual Salary
|
|
Dunaway
|
Faye
|
A1234
|
Accounting
|
$154,330
|
|
Williams
|
Tennessee
|
G2342
|
Accounting
|
$225,000
|
|
Beatty
|
Warren
|
K8495
|
Shipping
|
$360,000
|
|
Your Last Name
|
Your First Name
|
L9901
|
IT
|
$999,999
|
|
Williams
|
Vanessa
|
M8978
|
Shipping
|
$267,500
|
|
Presley
|
Elvis
|
P5544
|
Sales
|
$1
|
|
Williams
|
Serena
|
T9894
|
Sales
|
$345,000
|
|
Kimmel
|
Jimmy
|
U3456
|
Accounting
|
$77,800
|
|
Presley
|
Priscilla
|
Z9876
|
IT
|
$343,000
|
|
Clients
|
|
Firm Name
|
Address
|
State
|
Credit
|
|
COSTCO
|
4567 Executive Drive
|
OR
|
Good
|
|
Fordham U.
|
441 E. Fordham Road
|
NY
|
Excellent
|
|
Kmart
|
1010 Upper Sears Drive
|
IL
|
Fair
|
|
Target
|
431 Hanging in There Blvd.
|
CA
|
Poor
|
|
Tino's
|
2410 Arthur Avenue
|
NY
|
Fair
|
|
Walmart
|
1298 Lower Price Street
|
TX
|
Okay
|
Each client has one representative in our company. Some of our employees are client representatives, while others are not.
Currently, Elvis Presley has been assigned three clients: Tino's, Target, and Kmart; Warren Beatty has been assigned COSTCO and Walmart; and Priscilla Presley has been assigned Fordham.
Your boss has given you the following assignment. Read the whole assignment before you begin.
1. For Employee L9901 replace the first and last names with YOUR first and last name.
2. Create (that is, define the structure of) the necessary tables to define this database in Access. Give the entire database this name: PLENUM.yourfirstname.yourlastname
Be sure to designate appropriate primary keys and to set appropriate field types and sizes. For salary, use field type currency.
IMPORTANT HINTS:
- Planning the structure of your database--e.g., sketching it out on paper--would be a good way to start before defining it in Access.
- Make sure that if you use a foreign key, it is of the same data type as the field to which you are linking.
- Ask yourself what type of relationship it is (1-1, 1-many, many-to-many?)
- Hint: See Microsoft Access Lab Exercises #2 and #3 if you need help.
3. Create a relationship that is needed for this database. Be sure to enforce referential integrity.
4. Enter the data for employees and client firms into the database if you have not done so already.
5. Use the Query capability to answer the following questions. For each question, include only the information requested and show fields in the order they are listed in the question. Save your query in the database. (Name your queries by question--e.g., 5a, 5b, and so forth.).
(Hint: Do not worry about the format of the results, but do make sure that all the data show--i.e., nothing is chopped off. You can just drag the boundary line that divides the columns in datasheet view to change the width of columns in a datasheet.)
IMPORTANT: Your queries should be general enough to answer these questions correctly no matter what data are in the database. For example, even if your data already happen to be in the order requested by a particular question, still perform the sort command. (If this were a large database, you would have no way of knowing that the data were already in the correct order.)
a. Display, in alphabetical order, the full names and annual salaries of all employees earning more than $165,000 per year. (Hint: Pay attention to first and last names. Another Hint: Enter it as 165000 without $ or ,)
b. Display the full names and annual salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line).
c. Display the full names and MONTHLY salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line). (Hint: See pages 156-161 of the (SV) New Perspectives on Microsoft Access lab manual.)
Do not worry if your display for the monthly salary column is not very nice, with lots of decimal places. But if you want to clean this up, right click in this column in Design View and then choose "Properties." You will get a "Property Sheet" pane on the right side of your screen. Select "Currency" as the "Format" and 0 for "Decimal Places."
d. Display the full names and the annual salaries of all employees in Shipping (don't show the Shipping field).
e. Display a list of departments, showing for each department how many employees it has (that is, the count of employees) and their average salary.
f. For the Accounting department, display the count of employees and their average salary (don't show the Department field).
g. Display a list of client firms matched with their representatives. (Make sure to display all the fields pertaining to these clients and employees, but do not display any field twice.)
h. Display a list of client firms matched with their representatives and sorted by the representatives' employee IDs. Show only the firm name, the representative's first and last names, and the representative's ID.
i. For those employees who are client representatives, list (in alphabetical order) their full names and how many firms they are assigned.
6. Be sure to close your database when you are finished. Your database must be closed to upload it to Blackboard.