CS 3 - Kurtz Santa Monica College

Database Assignment



1.      Create a database file named PAYROLL.MDB.

Create a Table

2.      Create and save a table named PAYROLL LIST with the following fields:

Field Name          Data Type          Field Size
Last Text 15
First Text 15
Dept Text 20
Hours Number Integer
Rate Currency


3.      Enter these four records into the table:

Rec          Last          First Dept          Hours          Rate
1 Brown Larry Sales 45 10.25
2 White Eileen Sales 37 9.75
3 Silver Steven Accounting 39 8.75
4 Gold Paul Personnel 35 8.25


4.      Save and close the table.

Create a Form

5.      Create and save a form named EMPLOYEE PAYROLL FORM.

6.      Enter Records 5 through 8 using the Form window:

Rec          Last          First Dept          Hours          Rate
5 Brown Paul Accounting 35 7.50
6 Black Susan Personnel 42 8.25
7 Green Caryn Accounting 38 6.75
8 Indigo Jessica Personnel 43 5.25


7.      Add a record with your name.  Assume that you are an employee in Personnel who worked 41 hours at a rate of $9.75.

8.      Save and close the form window.

9.      Search for all Dept fields that display “Personnel” and replace them with “Human Resources”.

Create a Query


10. Create a query with the names and rates of employees whose department is Human Resources and who worked more than 40 hours. Save the query as HR OVERTIME.


11. Create a query to limit searches to employees in Accounting whose rate is $7.50 or more. Use the query to list those employees, showing all fields. Save the query as ACCTG RATES.


13. Use the Update Query feature to update the database by reducing the HOURS field by 5.  Save the query as HOURS.



Create a Report


14. Create a report form named PAY REPORT. Create columns that display each of the five fields in the structure (Last, First, Dept, Hours, Rate). Group data in the DEPT field and sort in alphabetical order by last and then first name.