1. Create a new, blank Microsoft Access file
2. After your Microsoft Access database file is created and securely saved, create a table with the following fields:
o Employee ID: Use the AutoNumber data type with New Values set to Random and set this field as the primary key.
o Prefix: Use the Lookup Wizard to specify a list of values including Mr., Ms., Mrs., and Dr. for this field and ensure that you limit the input to this list only.
o First Name: Use the Text data type.
o Middle Name: Use the Text data type.
o Last Name: Use the Text data type.
o Address: Use the Text data type.
o City: Use the Text data type.
o State: Use the Lookup Wizard to specify a list of values including at least five state abbreviations for this field and ensure that you do not limit the input to this list.
o Zip: Use the Text data type with zip code input mask.
o Home Number: Use the Text data type with telephone number input mask.
o Cell Number: Use the Text data type with telephone number input mask.
o E-mail Address: Use the Text data type.
o Hire Date: Use the Date/Time data type with short date input mask.
3. After all the fields are created, name or rename the table as Employees and close the table but not the database.
4. Create a form based on the Employees table by using the Form Wizard. Include all the fields in the form except the Employee ID field.
5. Select Columnar when prompted to choose the layout of the form. In the last step, select Modify the form's design and set the form title to Employee Data Entry Form and center-align this title.
6. On the left navigation panel, where objects can be accessed, right-click the form and rename it to Form: Employees.
7. Now, open the form and enter at least ten fictional employee records.
8. After all the records are entered, close the form and then double-check the Employees table to ensure that the ten records are updated.
9. Now, use the Simple Query Wizard to create a query based on the Employees table. Include the Prefix, First Name, Middle Name, Last Name, and Hire Date fields. Run the query to ensure that it works and then close the query.
10. On the left navigation panel, where objects can be accessed, right-click the query to rename it to Query: Employee Hire Date.
11. Now, use the Report Wizard to create a simple report based on the Query: Employee Hire Date and include all the fields. Sort the report in the Descending order of Hire Date. Select Tabular from the Layout group box and Landscape from the Orientation group box.
12. In the last step of the Report Wizard, select Modify the report's design and change the title to Employee Hire Date. Run the report to see how it looks, and explore the design area of the report to fine-tune field sizes for properly balancing the report output.
13. On the left navigation panel, where objects can be accessed, right-click the report to rename it to Report: Employee Hire Date.
14. Before closing the database, go through and review your table, form, query, and report to ensure that they all work and then close the database.
Now that you have developed the Employees database in Week 4 Project, your Project Manager has asked you to expand this database as a tool for generating various reports associated with salary, weekly payroll, evaluations, and safety. During an official meeting, your Project Manager asked for the following reports to be developed and managed through the Employees database:
• A summary showing each employee's discipline and the current pay rate
• A weekly summary of employee payroll
The Project Manager, during the meeting, indicated having extensive experience in using Microsoft Access and suggested using the following steps as a guide to create these reports:
1. Open the Employees database created in Week 4 Project, and create a new table with the following fields:
o Job Code ID: Set this field as the primary key and then use the Lookup Wizard to specify a list of values for this field including EH, EJ, EF, EGF, IH, IJ, IF, IGF, PM, and SE. Do not limit the input to this list.
o Employee Discipline: Use the Lookup Wizard to specify a list of values for this field including Electrical Helper, Electrical Journeyman, Electrical Foreman, Electrical General Foreman, Iron Helper, Iron Journeyman, Iron Foreman, Iron General Foreman, Safety Engineer, and Project Manager. Do not limit the input to this list.
o Pay Rate: Use the Currency data type.
2. After all the fields are created, name or rename the Job Codes table and close the table but not the database.
3. Next, use the Form Wizard and create a form based on the Job Codes table. Include all the fields and use the Tabular layout for the form. Open the form in Design View to modify and adjust the format as desired.
4. Close the form and rename it to Form: Job Codes. Then, reopen it and enter the following information:
Job Code ID Employee Discipline Pay Rate
EH Electrical Helper $12.95
EJ Electrical Journeyman $19.25
EF Electrical Foreman $21.20
EGF Electrical General Foreman $23.50
IH Iron Helper $11.25
IJ Iron Journeyman $16.25
IF Iron Foreman $18.50
IGF Iron General Foreman $19.75
SE Safety Engineer $25.00
PM Project Manager $45.00
5. After the data is entered through the form, close the form, and check the Job Codes table to ensure that the records were successfully entered and updated in the table.
6. Close the Job Codes table and open the Employee table. Add a Job Code ID field to this table so Employees and Job Codes can have a common field. For the Data Type, choose the Lookup Wizard.
o Choose: I want the lookup field to get the values from another table or query. Next.
o "Which table or query should provide the values for your lookup field?" From the Table View, choose the Table: Job Codes. Next.
o From the next screen, choose the following Available Fields: Job Code ID and Employee Discipline. Next.
o Sort Ascending by whichever field you think makes the most sense to you. Next.
o With the next screen (How wide would you like the columns in your lookup field), I suggest unchecking the "Hide key column" so you can see it. In future applications, you might want to hide it. You can double click the right border of the columns to auto fit them to the longest value. Next.
o The next screen asks you which fields' values you want to store. Choose the Job Code ID. Next.
o Use "Job Code ID" without the quotation marks for the label for your lookup field. Finish.
7. Then, go to Database Tools and select Relationships. Establish a relationship from Job Code ID (primary key in the Job Codes table) to Job Code ID (foreign key in the Employees table) and Enforce Referential Integrity.
8. Open the Employees table and in the Job Code ID field, click on the drop down arrow and choose a Job Code ID/Employee Discipline for each employee.
9. Next, create a new table with the following fields, and when prompted to save the table, use the name Employee Payroll. Do not set a primary key.
o Employee ID: Use the Number data type, and then use the Lookup Wizard to specify a list of values based on the Employees table. When selecting fields from the Employees table, select Employee ID first and then the First, Middle, and Last Names. Do not hide the key column. Also, sort the field by last name and enable data integrity.
o Total Hours Worked: Use the Number data type.
o Period Beginning: Use the Date/Time data type with short date input mask.
o Period Ending: Use the Calculated data type with the expression [Period Beginning]+6.
10. After the Employee Payroll table is complete, close it. Then, use the Form Wizard to create a form based on the Employee Payroll table. Include all the fields, and use Tabular for the layout of the form. Open the form in the Design View to modify and adjust its format as desired. Name the form Form: Employee Payroll.
11. Using Form: Employee Payroll, enter at least twenty-to-thirty records, and for some selected employees, be sure to include at least three or four different week-ending dates. After the records are entered, close the form.
12. Add a Relationship (in Database Tools) between the Employee Payroll table and one of the other tables in the Relationship window with a common field. Think this through, which would be the one and which would be the many in the relationship? Enforce referential integrity.
13. Using the Employee Payroll, Employees, and Job Codes tables, create a query including the following field extractions:
o Employee ID: From Employees
o Period Beginning: From Employee Payroll
o Period Ending: From Employee Payroll
o First Name: From Employees
o Middle Name: From Employees
o Last Name: From Employees
o Pay Rate: From Job Codes
o Total Hours Worked: From Employee Payroll
14. In the same query, include the following calculated expressions and input them in the Field locations within the query design:
o Overtime Hours: IIf([Total Hours Worked]>40,[Total Hours Worked]-40,0)
o Straight Pay: ([Total Hours Worked]-[Overtime Hours])*[Pay Rate]
o Overtime Pay: [Overtime Hours]*([Pay Rate]*1.5)
o Gross Pay: [Straight Pay]+[Overtime Pay]
o Social Security Tax: [Gross Pay]*0.125
o Federal Withholding Tax: IIf([Gross Pay]>=1500,[Gross Pay]*0.2,IIf([Gross Pay]>=1000,[Gross Pay]*0.15,IIf([Gross Pay]>=500,[Gross Pay]*0.1,IIf([Gross Pay]<500,[Gross Pay]*0.05))))
o State Withholding Tax: IIf([Gross Pay]>=1500,[Gross Pay]*0.1,IIf([Gross Pay]>=1000,[Gross Pay]*0.075,IIf([Gross Pay]>=500,[Gross Pay]*0.05,IIf([Gross Pay]<500,[Gross Pay]*0.025))))
o Net Pay: [Gross Pay]-([Social Security Tax]+[Federal Withholding Tax]+[State Withholding Tax])
o Earned Vacation Hours: [Total Hours Worked]*0.05
15. Run the query to ensure that all the fields and expressions yield values. For the values that represent money, ensure that the Currency format is used. In case the Currency format is not available, go back to the design of the query, right-click the associated field location, and from the shortcut menu, select Properties. In the Property Sheet, the Format options to adjust to Currency are available. Do this for all cases where the Currency format is needed.
16. Close the query and rename it to Query: Employee Payroll.
17. Using Query: Employee Payroll, create another query including the following field extractions:
o Employee ID
o Week Ending Date
o First Name
o Middle Name
o Last Name
o Total Hours Worked
o Net Pay
18. In the Net Pay field, include a criterion to show only the employees who have worked over 40 hours. Run the query to ensure that it yields correct output, close the query, and rename it to Query: Employee Payroll Over 40 Hours.
19. Now, using Query: Employee Payroll as the record source, create a report as specified in the provided design. Use the steps given from point 16.
20. Create the report by using the Report Wizard and include the following fields in the given order:
a. Employee ID
b. Period Ending
c. Last Name
d. Gross Pay
e. Total Deductions
f. Net Pay
21. Group the report by Employee ID.
22. Sort the report in ascending order using the Last Name field. For the summary options, calculate the totals for Gross Pay, Total Deductions, and Net Pay by using the option SUM.
23. Use Stepped for the layout, and use Landscape for orientation. Also, adjust the width of the fields so that all the fields fit on a page.
24. For the title, use Report: Employee Payroll Summary, and then preview the report.
25. The report should now resemble the provided design in Design View. Use the steps from point 22 to further enhance the format of the report. You can use your own ideas for formatting the report.
26. While still in the Design View, right-click the label with the information "="Summary for " & "'Employee ID' = " & " " & [Employee ID] & " (" & Count(*) & " " & IIf(Count(*)=1,"detail record","detail records") & ")" and from the shortcut menu, select Delete because this information is not needed in the report.
27. Next, select all the information up to the page header, and then right-click and select Properties from the shortcut menu.
28. On the Format tab of the Property sheet, set the following properties:
o Width: 1.5
o Height: 0.25
o Border Style: Transparent
29. While labels and data are still selected, go to the Home ribbon and use the Center tool to center-align all the labels and values. Then, click each item separately, as needed, to customize its placement in the report design. You can use the provided design as a guide.
30. Next, select the report title and widen the text area across the range of the report. Then, go to the Home ribbon and use the Center tool to center-align the title.
31. Next, under the Employee ID Footer, click the Sum label and rename it to Employee Totals. Then, deselect this label and right-click it. Using the shortcut menu, fill with a color of your choice. Use the same color to fill in the calculated values.
32. Next, under the Report Footer, click the Grand Total label and rename it to Total Payroll. Then, deselect this label and right-click it. From the shortcut menu, select a color of your choice to fill. Use the same color to fill in the calculated values.
33. Next, under the Report Footer, select the calculated fields, and using the Property sheet and the Format Tab, set the format to Currency.
34. Next, under the Employee ID Footer, select the calculated fields, and using the Property sheet and the Format Tab, set the format to Currency.
35. Compare your output to the design illustrated above and, if necessary, revisit the Design View of the report to fine-tune your report by making adjustments through manual formatting.
36. Review your report and close it.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.Report: Employee Payroll Summary
Employee ID Last Name Period Ending Gross Pay Net Pay Deduction
-1735847438 Keppin 4/21/2019 $466.40 $373.12 $93.28
Keppin 3/7/2019 $424.00 $339.20 $84.80
Employee Totals $890.40 $712.32 $178.08
-1163361526 Willatts 3/10/2019 $2,138.50 $1,229.64 $908.86
Willatts 1/8/2019 $1,750.75 $1,006.68 $744.07
Willatts 10/28/2019 $2,984.50 $1,716.09 $1,268.41
By purchasing this solution you'll be able to access the following files:
Solution.accdb, Solution1.accdb and Solution.docx.