Access Project (attached)
To: Analytics Consulting
From: KNU Manufacturing Company
Re: Payroll System Development Project
We appreciate your assistance in helping us to start creating a payroll system along with the appropriate queries, forms and reports that will meet our company's needs. Below are some of the details that we have identified as necessary for our system, but please feel free to ask questions if you need additional information or specifications.
1. We need three tables that contain information regarding: 1) employees, 2) wages, and 3) hours worked for each week of a given year. The following information should be included in the tables:
EMPLOYEE ID LAST NAME FIRST NAME SSN STREET ADDRESS CITY STATE ZIP DATE HIRED
AUTO Lemmon John 564-58-9834 411 Colfax Ave Denver CO 80010 2/8/2011
AUTO Pesto Ann 583-23-3548 819 16th Ave Denver CO 80002 6/8/2011
AUTO Farmer Paul 213-54-8195 9800 Oakland Blvd Denver CO 80012 9/30/2010
AUTO Walker Sarah 322-45-2153 8104 Bennington Dr Denver CO 80033 5/5/2010
AUTO Milonea Simon 984-51-4537 6219 Lister St Denver CO 80022 2/9/2010
b. WAGE_INFO c. HOURS_WORKED
SSN WAGE RATE SALARIED
564-58-9834 $28 Y
583-23-3548 $12.50 N
213-54-8195 $29 Y
322-45-2153 $14 N
984-51-4537 $12.50 N
SSN WEEK NO HOURS
564-58-9834 1 40
564-58-9834 2 50
583-23-3548 1 45.5
583-23-3548 2 48.75
213-54-8195 1 49
213-54-8195 2 46
322-45-2153 1 48.25
322-45-2153 2 46.5
984-51-4537 1 55.25
984-51-4537 2 50.75
2. Can you please create queries to help with analyzing/solving the following issues:
a) We need to analyze wages for week 1 by calculating gross pay for hourly employees.
b) We need to analyze what wage rates would be for hourly employees if all received a 5% increase to determine if raises can be given.
c) We are analyzing wages and salaries and need to determine the average wage rate grouped by hourly/salaried position.
d) We need to determine if bonuses can be paid this year. Bonuses would be calculated as $2 for each day the employee has worked at the company.
e) We need to update our hourly wages to reflect a $.35 raise for all hourly employees (and we would like this in a separate file from the original file you create)
f) We want to analyze, on a recurrent basis, the number of hours an employee worked by typing in the SSN for the respective employee.
KNU Manufacturing Company payroll system development projects are examined.