complete the following discussions for adding to or updating data in a table which do you think would be the most useful a wizard report query or form why there may be more than one way to add a column to a table which way do you prefer why wha
Part A
Customizing Table Design
Complete the following discussions:
For adding to or updating data in a table, which do you think would be the most useful—a wizard, report, query, or form? Why?
There may be more than one way to add a column to a table. Which way do you prefer? Why?
What does customizing fields and tables mean? Why do you think it is necessary to customize fields and tables? How often do you think you would need to customize fields and tables? Give reasons to support your answer.
What is database normalization and why is this concept so important to consider when designing a table?
Part B
Employees Database (Part 2 of 2)
Scenario
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:
Open the Employees database created in Week 4 Project, and create a new table with the following fields:
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.
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.
Pay Rate: Use the Currency data type.
After all the fields are created, name or rename the Job Codes table and close the table but not the database.
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.
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
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.
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.
Choose: I want the lookup field to get the values from another table or query. Next.
“Which table or query should provide the values for your lookup field?” From the Table View, choose the Table: Job Codes. Next.
From the next screen, choose the following Available Fields: Job Code ID and Employee Discipline. Next.
Sort Ascending by whichever field you think makes the most sense to you. Next.
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.
The next screen asks you which fields’ values you want to store. Choose the Job Code ID. Next.
Use “Job Code ID” without the quotation marks for the label for your lookup field. Finish.
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.
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.
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.
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.
Total Hours Worked: Use the Number data type.
Period Beginning: Use the Date/Time data type with short date input mask.
Period Ending: Use the Calculated data type with the expression [Period Beginning]+6.
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.
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.
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.
Using the Employee Payroll, Employees, and Job Codes tables, create a query including the following field extractions:
Employee ID: From Employees
Period Beginning: From Employee Payroll
Period Ending: From Employee Payroll
First Name: From Employees
Middle Name: From Employees
Last Name: From Employees
Pay Rate: From Job Codes
Total Hours Worked: From Employee Payroll
In the same query, include the following calculated expressions and input them in the Field locations within the query design:
Overtime Hours: IIf([Total Hours Worked]>40,[Total Hours Worked]-40,0)
Straight Pay: ([Total Hours Worked]-[Overtime Hours])*[Pay Rate]
Overtime Pay: [Overtime Hours]*([Pay Rate]*1.5)
Gross Pay: [Straight Pay]+[Overtime Pay]
Social Security Tax: [Gross Pay]*0.125
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))))
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))))
Net Pay: [Gross Pay]-([Social Security Tax]+[Federal Withholding Tax]+[State Withholding Tax])
Earned Vacation Hours: [Total Hours Worked]*0.05
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.
Close the query and rename it to Query: Employee Payroll.
Using Query: Employee Payroll, create another query including the following field extractions:
Employee ID
Week Ending Date
First Name
Middle Name
Last Name
Total Hours Worked
Net Pay
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.
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.
Create the report by using the Report Wizard and include the following fields in the given order:
Employee ID
Period Ending
Last Name
Gross Pay
Total Deductions
Net Pay
Group the report by Employee ID.
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.
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.
For the title, use Report: Employee Payroll Summary, and then preview the report.
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.
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.
Next, select all the information up to the page header, and then right-click and select Properties from the shortcut menu.
On the Format tab of the Property sheet, set the following properties:
Width: 1.5
Height: 0.25
Border Style: Transparent
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.
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.
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.
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.
Next, under the Report Footer, select the calculated fields, and using the Property sheet and the Format Tab, set the format to Currency.
Next, under the Employee ID Footer, select the calculated fields, and using the Property sheet and the Format Tab, set the format to Currency.
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.
Review your report and close it.
Next, right-click Report: Employee Payroll Summary label located in the objects navigational area on the left. From the shortcut menu, export the report as an .rtf file in Microsoft Word and save it on your desktop for easy access. Close the database.
Submission Details
Note: In this assignment, you used the database from Week 4 Project and it is named EU_ITS2021_W4_Project_LastName_FirstInitial.accdb.
Name your Microsoft Word document EU_ITS2021_W5_Project_LastName_FirstInitial.doc.
Name your export report file as EU_ITS2021_W5_Project_LastName_FirstInitial.rtf.
Submit your database, Microsoft Word document, and report file.
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount! Use Discount Code “Newclient” for a 15% Discount!NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
The post complete the following discussions for adding to or updating data in a table which do you think would be the most useful a wizard report query or form why there may be more than one way to add a column to a table which way do you prefer why wha appeared first on Superb Professors.
For any questions, feedback, or comments, we have an ethical customer support team that is always waiting on the line for your inquiries.