BIS 2003
Access Project
(100 pts.)
You are still
working for the Banker Company. After you finished the Excel spreadsheets,
management was so impressed that they asked you to continue working with the
same data using an Access database. Management has given you an Access database
with the following tables: District, Employee, Ethnic, Performance, Salary
History, and Stock Option. Since this database is in its early stages of
development, a lot of work must be completed before management can receive
useful reports.
Step 1: Tables
(20 pts). Two additional
tables need to be added to the database as follows:
(1a) Create a
“Job Title” table – Include 4 fields, Job Classification Code (text),
Job Title (text), Minimum Salary (currency), and Maximum Salary (currency); be
sure to set a primary key. Data records should be entered as follows for
job classification code, job title, minimum salary, and maximum salary,
respectively:
1A, Level 1 Knowledge Worker, 16500, 28000;
2B, Level 2 Knowledge Worker, 20500, 37500;
3C, Service Manager, 27000, 57000;
4D, Operations Manager, 32000, 62000;
5E, VP Service, 57000, 84000;
6F, VP Operations, 65000, 122000;
7G, CFO, 74455, 302000;
8H, CIO, 95000, 525000;
9I, CEO, 110000, 695000.
(1b) Import the “State” table –
import the State sheet from the Excel project file. Be sure to set “State
Code” as the primary key.
Step 2: Data
Validation (10 pts). Data
validation can be done throughout a database much the same way as in a
spreadsheet. To illustrate how validation can be done, complete the following:
(2a) Lookup
Function – In the Employee table, use the lookup function to tie the District
Code field to the District table (District Code field). (This will limit users
to entering district codes that only exist in the District table into the
Employee table.)
(2b) Validation
– In the Stock Options table, add a validation rule and a
validation message that will only allow the Stock Shares Issued
to be between 0 and 350,
inclusive.
Step 3:
Relationships (15 pts). Complete the relationship setup. Relationships
have already been established for some tables in the database. Therefore, you
only need to establish relationships for tables that have not been added to the
relationship network. You must
enforce Referential Integrity for each relationship. Queries, forms,
and reports cannot be started until this step is complete.
·
Job
Title: Job Classification Code à
Salary History: Job Classification
·
State:
State Code à Employee: State Code
·
District:
District code à Employee: District code
Step 4: Queries
(35 pts). Management has
requested a number of quick answers to questions of interest to the company.
Complete queries for each of the following: (be sure to format data appropriately,
for example, $ and %;)
(4a) A count of the number of employees by job
title in 2021. Do not show the year in the results. Rename the
Employee ID as Total Employee Count. Save the query as: 4a-Per Job Title Count.
·
Hint:
This is a grouping query. You would need to include the Totals Row.
(4b) A list of all employees (ID, last name, first
name, and job title) whose salary ranges between $65,000 and $130,000 in 2022.
Sort by Salary (ascending). Do not show the year in the results. Save the query
as: 4b-Job Sort by Salary 2022.
(4c) The highest salary paid by the company in 2021.
(The query should show a single number, and that is all; do not show the year.)
Rename the Salary as The Highest Pay of 2021. Save the query as: 4c-The Highest
Pay of 2021.
·
Hint:
This is a grouping query. You would need to include the Totals Row.
(4d) The average salary paid by year, gender, and
ethnic name. Sort by year first (descending), gender second (ascending),
and ethnic name third (descending). Rename the Salary as Avg Salary. Save the
query as: 4d-Avg Salary.
(4e) User
Input Box – Tom Jones’ performance rating in 2021. Use a user input
box to allow the user to input the employee’s first and last name, and receive
that employee’s performance rating name in 2021. Use Tom Jones as an example, only show the performance
rating name as the output. Save the query as: 4e-Input Box.
(4f) Computed Fields – A list of each employee’s
new salary, calculated based on the performance increase policy below, and
salary ranges for 2022. Show the employee ID, last and first name,
salary (for 2021), new salary (for 2022), and “out of salary range?”;
do not show the year. Use a computed field in the query to calculate the
new salary (for 2022) based on 2021 salaries via Expression Builder. Rename the
salary column to “2021 Salary” and the new salary column to “2022
Salary”. Use a computed field in the query (using the IIF function) to
determine if the new salary falls within the minimum and maximum salary, given
the employee’s job classification code. Name the column “Out of Salary
Range?”. If the salary is out of range, the cell should say “yes”;
otherwise, leave the cell blank. Save the query as: 4f-New Salary.
·
Hint:
New salary calculation rules should be [Performance]![Percentage]*[Salary
History]![Salary]+[Salary History]![Salary]
(4g) Employees’ complete information list for 2022. Show employee ID, last name, first
name, ethnic name, job title, and salary (in that order) for all employees with
2022 salary data in the database. Do not show the year in your results. Sort by
employee ID (ascending). Save the query as: 4g-Employee Info 2022.
Step 5. Forms
(10 pts). Forms are used
to allow easy data entry for users. Forms are designed for users who may not be
familiar with the database. Therefore, we must try to simplify data entry as
much as possible.
(5a) District Update Form – Create a
form that will allow new district codes and names to be added to the District
table. Save the form as: 5a-District Update Form. Test your form by entering
the following new code and name, respectively: SW, Southwest.
(5b) Stock
Update Form (using a Subform) – Create a form with a subform that
will allow the user to enter new stock options for each employee. The form
should contain all data fields from the Employee table. The subform should
contain all fields from the Stock Options table. Do not allow users to modify any
fields in the Employee form. Save the form as: 5b-Stock Update
Form. Save the subform as: 5b-Stock Update
Subform.
Step 6.
Reports (10 pts). Reports
are used to summarize material for management. Be sure to format data
appropriately, for example, $ and %, and include
an appropriate title on each report.
(6a) Employment
Report – Create a report from the query in step 4g. View by Salary History.
Sort by employee ID (ascending). It is recommended to use the Columnar layout. Show
only one employee per page of the report so that that page of the report can be
given to its respective employee. Be sure the report title appears on every
page. Save the report as: 6a-Employment Report.
(6b) Your
Report – Create a report that will be useful to management (i.e., use your
imagination). This report must be created from a query, but you may NOT use the
queries you completed in Step 4. You must create a new query for this report. Save
the report as: 6b-Mgmt Report. Save the new query as: 6b-Mgmt Query.
NOTE: Name your Access project as yourlastname_firstname.
Very Important
Note: You must properly save your file and Exit Access before uploading it
to Harvey. After your submission, make sure the uploaded file is correct and
readable. You may download your submitted assignment via Harvey and confirm
that it is readable on your device. If I cannot open or read your file, you
will receive a zero for this assignment.
