MIS 2003
Access Project
Spring 2015
You are still working for the Jacobson Company. After you finished the Excel spreadsheets, management was so impressed that they have 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, Minority, 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 – two additional tables need to be added to the database as follows: (4 pts. each)
(1a)Create “Job Title” table – Include 4 fields, Job Classification Code (text; 2 characters in length), Job Title (text; 40 characters in length), Minimum Salary (currency), and Maximum Salary (currency); be sure to include descriptions for each field and to set a primary key. Data records should be entered as follows for job classification code, job title, minimum salary and maximum salary, respectively:
A1, Level 1 Knowledge Worker, 14500, 26000;
B2, Level 2 Knowledge Worker, 18500, 35500;
C3, ServiceManager, 25000, 55000;
D4, Operations Manager, 30000, 60000;
E5, VP Service, 55000, 82000;
F6, VP Operations, 63000, 120000;
G7, CFO, 72455, 300000;
H8, CIO, 85665, 500000;
I9, CEO, 95000, 675000.
(1b)Import “State” table – import the State sheet from the Excel project. Be sure to enter descriptions for each field and to set a primary key.
Step 2: Relationships – Create relationships for Step 1 Tables. Relationships have already been established for the pre-existing tables in the database, therefore you only need to establish relationships for the two new tables added in Step 1. Queries, forms, and reports cannot be started until this step is complete. (4 pts.)
Step 3: Data Validation – Data validation can be done throughout a database, in much the same way that it was done in a spreadsheet. In order to illustrate how validation can be done, complete the following: (3 pt. each)
(3a)Lookup Function – In the Employee table, use the lookup function to tie the Minority Code field to the Minority table. (This will limit the user to enteringminority codes that exist in the Minority table into the Employee table.)
(3b) Lookup Function – In the Salary History table, use the lookup function to tie the Performance Rating field to the Performance table.
(3c)Validation – In the Stock Options table, add a validation rule and validation text that will only allow the Stock Shares Issued to be between 0 and 275, inclusive.
Step 4: Queries – 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 %; all queries must be derived from tables, and NOTfrom other queries) (4 pts. each)
(4a)A count of the number of employees in each district (use thedistrict name).Save the query as: 4a-DistrictCount.
(4b)A list of all employees (ID, last name and first name) with their job title for 2014.Sort by job title(descending). Do not show the year in the results. Save the query as: 4b-Job Sort.
(4c)Thelowest salary paid by the company for 2014. (The query should show a single number as a result and that is all; do not show the year.) Save the query as: 4c-LowestSalary.
(4d)Theaveragesalary paid by year,performance rating, and gender.Sort by performance ratingname first (ascending), gender second (ascending), and year third (descending). Save the query as: 4d-AvgSalary.
(4e)A list of all employees (ID, last and first name) and their stock shares issued by year. Sort so those with stock sharesare first, followed by those that do not have stocks. Save the query as: 4e-Stock Options.
(4f)User Input Box –Lionel Messi’sperformance rating in 2014. 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 for 2014 as a result. Only show the performance ratingname in the result. Save the query as: 4f-Perf Rating.
(4g)Computed Fields – A list of each employee’s new salary, based on his/her performance increase, and salary ranges for 2014. Show the employee ID, last and first name, salary (for 2014), new salary (for 2015), and out of salary range?; do not show the year. Use a computed field in the query to calculate the new salary(for 2015) based on 2014 salaries. Rename the salary column to “2014 Salary” and the new salary column to “2015 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: 4g-New Salary.
(4h)Employees’ full information for 2014. Show employee ID, last name, first name, minority name, district name, job title, and salary(in that order) for all employees with 2014salary data in the database. Do not show the year in your results. Sort by employee ID (ascending). Save the query as: 4h-Employee Info.
Step 5. Forms – forms are used to allow easy data entry for users. Management would like to start by having two forms produced. Additional forms will be added to the database at a later date. Remember that forms are designed for the user, who may not be familiar with the database; therefore, we must try to simplify data entry as much as possible. Be sure that all forms have a clear title (cannot be the name you are saving the form as) and instructions for the user regarding how to use the form.Failure to use an appropriate title will result in a score of zero on the question.(4 pts. each)
(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: NW, Northwest.
(5b)Stock Update Form (using a Subform) – Create a form with 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. Allow the user to only modify fields in the Stock Options subform, excluding the Employee ID, and modify none of the fields in the Employee form. Save the form as: 5b-Stock Update Form. Save the subformas: 5b-Stock Subform.
Step 6. Reports – reports are used to summarize material for management. Management would like to receive many reports in the future, but has only requested two reports at this time. Be sure to format data appropriately, for example, $ and %, and include an appropriate title on each report (do not use the name you are saving the report as).Failure to use an appropriate title will result in a score of zero on the question.(5 pts. each)
(6a)Employment Report – Create a report from the query in problem 4h. The report should be sorted by employee ID (ascending). Show only one employee per page of the report so that that page of the report can be given to its respective employee in the company. Be sure the report title appears on every page. Savethe 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.
Step 7. Explanations –Briefly explainwhat the reports (in Step 6) tell management and how management might use the reports in decision making. Place your explanations at the bottom of each report in Access (by using a label). (2 pt. each)
NOTE: You must name your Access project: yourlastname_firstname_classtime.accdb. (Note: the file extension is “.accdb”, not “.mdb”; it must have the correct file extension.) Failure to do so will result in a 5-pt deduction in your project score. If working in teams, include all student names in the file name.