exam 1 for cis110 class

Part 1 – Create tblJobs Table

Review the page on Entities & Attributes for more information on design.

1. Start Access, create a new blank database, and create a table named tblJobs
2. Add 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.
3. Add the following fields.
   • Job Title
   • Hourly Pay – field must be able to contain fractional dollar amounts: example 10.75
   • Hours – 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.5
4. Add 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.
5. 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. Example
6. Enter a description for each field to explain what it is used for. Example

Part 2 – Input tblJobs Data

1. Input 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. Example
2. Set the hourly pay rate for the Owner as $49.50 and the Trainees at $15.25
3. Set 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.
4. 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 Table

1. Create a table named tblEmployees
2. Add a field named EmployeeID as the Primary Key (PK) and set its type as AutoNumber.
3. 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 name
   • address (including all fields necessary for a complete mailing address)
   • hire date – date hired at company
   • married – indicates employee is currently married
   • insurance – indicates if the employee either receives or does not receive health insurance
   • number of dependents – number of dependents under the age of 26
4. Set 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.
5. Enter a description for each field to explain what it is used for.

Part 4 – Input tblEmployees Data

1. Input Aiham Asmroo as one of the 12 employees.
2. 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 Example

1. Set 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 Jobs

1. Create a Foreign Key in one of the tables.
   • You have to decide which table based on how you are relating the tables.
2. 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.
3. Set the relationship to Enforce Referential Integrity and Cascade Update Related Fields
4. Assign the Owner job to yourself.
5. Assign jobs to the remaining 11 employees as follows:2 Managers, 1 Accountant, 6 Clerks, and 2 Trainees.
6. Save your file as Aiham.Asmroo_Exam1 Do not to use ANY spaces in the file name!

Part 6 – Create 2 Queries

1. Create a query displaying the following data using: first name and last name fields from tblEmployees and job title and hourly pay from tblJobs
2. Set the field order in the query from left to right as follows: last name, first name, job title, hourly pay
3. Sort the query design by hourly pay in descending order.
   • The query should only produce 12 records. View example
4. Save the query as qryEmployeePay.
5. 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.
6. Sort the query design by last name in ascending order.
7. Save the query as qryInsurance. View example

Part 7 – Create a Report

1. Create a report with the Report Wizard with qryInsurance as the report source using the defaults of the Report Wizard.
2. Change the title of the header caption from qryInsurance to Insurance Report
3. If necessary adjust the column headings so all column titles are completely visible.
4. Save the report with the name rptInsurance. View example

Part 8 – Finish and Submit

1. Set the database file option to compact on close.Not sure how to do this then watch the video on this page: Videos.
2. Clean up your database deleting any unused fields, tables, forms, and reports.
3. 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.