Part 1 – Create tblJobs Table
Review the page on Entities & Attributes for more information on design.Start Access, create a new blank database, and create a table named tblJobsAdd an Autonumber field to tblJobs to be used as the Primary Key (PK) uniquely identifying the jobs in your store giving the field an appropriate name.Add the following fields.Job TitleHourly Pay – field must be able to contain fractional dollar amounts: example 10.75Hours – field contains the weekly number of hours each job is scheduled, must be able to contain fractional hours typically between 20 to 40: example: 23.5Add at least 2 more fields which are relevant to the job, give them appropriate field names. You should have a minimum of 6 fields in the table.Set the correct data type / size for each field where the size is the set to the largest expected data attribute and type is set to match the data to be input. ExampleEnter a description for each field to explain what it is used for. Example
Part 2 – Input tblJobs DataInput 5 records into tblJobs using the following job titles; Owner, Manager, Clerk, Accountant, and Trainee.This tblJobs should only have 5 records since all employees with the same job have the same pay and work the same number of hours and so on. ExampleSet the hourly pay rate for the Owner as $49.50 and the Trainees at $15.25Set the number of hours the Owner works as 47.5, Trainees work less than 20 hours, set the values for the other employees to any reasonable value of 40 or less.Set hourly pay rate for each of the remaining job titles using a different hourly pay rate which is less than the owner pay rate but greater than the Trainees rate.
Part 3 – Create tblEmployees TableCreate a table named tblEmployeesAdd a field named EmployeeID as the Primary Key (PK) and set its type as AutoNumber.Add the fields necessary to store the data listed below in the table.The design must allow for sorting by these fields: first name, last name, city. You need to determine if 1 or more fields is required to hold each of the data elements listed and use an appropriate name and data type for those fields.The following data elements must be included in your design:employee nameaddress (including all fields necessary for a complete mailing address)hire date – date hired at companymarried – indicates employee is currently marriedinsurance – indicates if the employee either receives or does not receive health insurancenumber of dependents – number of dependents under the age of 26Set the correct data type and size for each field where the size is the set to the largest expected data attribute and not left at the default size.Enter a description for each field to explain what it is used for.
Part 4 – Input tblEmployees DataInput Aiham Asmroo as one of the 12 employees.Input records for remaining 11 employees using reasonable data which you make up so you have a total of 12 employees. IMPORTANT: Your employees must have EmployeeID numbers from 1 to 12. View ExampleSet the records for only 6 employees in the table to indicate that they have insurance.
Part 5 – Create Relationship Between Tables and Assign Employees to JobsCreate a Foreign Key in one of the tables.You have to decide which table based on how you are relating the tables.Create a relationship between tblEmployees and tblJobs such that a job can be assigned to 1 or more employees.Do not use the Job Title field to create the relationship! Not sure how to create a relationship? Watch these videos Relationships, Normalization or review the Relationships page.Set the relationship to Enforce Referential Integrity and Cascade Update Related FieldsAssign the Owner job to yourself.Assign jobs to the remaining 11 employees as follows:2 Managers, 1 Accountant, 6 Clerks, and 2 Trainees.Save your file as Aiham.Asmroo_Exam1 Do not to use ANY spaces in the file name!
Part 6 – Create 2 QueriesCreate a query displaying the following data using: first name and last name fields from tblEmployees and job title and hourly pay from tblJobs Set the field order in the query from left to right as follows: last name, first name, job title, hourly paySort the query design by hourly pay in descending order.The query should only produce 12 records. View exampleSave the query as qryEmployeePay.Create a query displaying only employees who have insurance with the following field data in this order: last name, first name, job title, insurance, number of dependents, city.Sort the query design by last name in ascending order.Save the query as qryInsurance. View example
Part 7 – Create a ReportCreate a report with the Report Wizard with qryInsurance as the report source using the defaults of the Report Wizard.Change the title of the header caption from qryInsurance to Insurance ReportIf necessary adjust the column headings so all column titles are completely visible.Save the report with the name rptInsurance. View example
Part 8 – Finish and SubmitSet the database file option to compact on close.Not sure how to do this then watch the video on this page: Videos.Clean up your database deleting any unused fields, tables, forms, and reports.Upload your Aiham.Asmroo_Exam1 database file using the Exam 1 link.You will not be able to upload your file if the name does not match the name listed in this step. Resubmitted files must use the same file name!Warning: Files can only be accepted via the class drop box. If you email your file to me then you automatically earn 0 for this assignment.Grading RubricPointsRequirements10Database follows correct database design principlesCorrectly named tables & fields (spelling counts!)PK & FK fields set, correct field types selectedtblEmployees EmployeeID field is an Autonumber fieldtables have correct number of fieldsField sizes set to expected data element sizeField types set appropriatelyDesign allows for sorting by first name, last name, or cityDesign has all fields for complete mailing addressNo extra objects (Records, Fields, Tables, Forms, or Reports)10tblEmployees has 12 records, with EmployeeID numbers from 1 to 12
tblJobs has at least 6 fields with only 5 records
Jobs assigned to employees according to instructions
Hourly pay values as set according to the assignment instructions6Correct relationship exists between tablesRelationship does not use the Job TitleEnforced Referential Integrity and Cascade Update8Created qryEmployeePay with correct fieldsCreated qryInsurance with correct fieldsFields are listed in the correct order Querydesign includes sorting according to instructions4Created rptInsurance with correct fields2Set database to Compact on Close40Total possible for uploading Aiham.Asmroo_Exam1