BISM 3400

/in /by

Copy the Staff_11 database included below onto your favorite storage device. Rename the file Staff_yourname.

PART A

1.Open the Employees table and observe the number of fields and records. Enter your own information as the last record with a title of RN and a birthdate of 9/1/85. Observe there are only three Title values for these employees: RN, Registered Nurse; MD Doctor of Medicine and DO Doctor of Osteopathic Medicine.

2.Close the Employee table and then open the Evaluation table. Observe the number of fields and records and then close the Evaluations table.

3.Create a new table named Titles using the Table Design View with two fields: Abbreviation (set as the primary key field) and Description. Change the data type for the Abbreviation field to Text.

4.Enter the three records into the Titles table to represent the three titles that are currently entered for the employees in the Employee table. Refer to Step 1 for help. Close the Titles table.

5.Create a new table named Dependents using the Table Design View with four fields: DepNo (set as the primary key field and as AutoNumber), DependentName as Short text, Spouse as Yes/No and EmployeeNo as Number. Close the Dependents table.

6.Create a new table named Compensation using the Table Design View with four fields: CompNo (set as the primary key field and as AutoNumber), CompensationYear as Date, Salary as Currency and EmployeeNo as Number. Close the Compensation table.

7.Open the Relationships window. Add the five tables to the window and the join the Titles, Dependents, Compensation, Employees and Evaluations tables together using the appropriate one-to-many relationships. Be sure to enforce referential integrity on all relationships.

8.Add records in the Dependent Table for all employees. Remember you must only employee numbers that are in the employee table. Close the Dependents table.

9.Add records to Compensation table for all employees. Remember you must only employee numbers that are in the employee table. Close the Compensation table.

10.Close and Exit Access.

11.Upload the file to the Assignment 1.

PART B

13. Create a query that contains the following information from the appropriate tables.

  •   EmployeeID
  •   First Name
  •   Last Name