Succeeding in Business with Microsoft Access 2013 Steps to Success Level 2

Succeeding in Business with Microsoft Access 2013 Steps to Success Level 2

Hudson Bay Pharmacy is ready to crearte more complex queries to analyze employee data. Kim Siemers, human resources manager for Hudson Bay, asks for your help in creating queries that extract the employee information she needs from their database. As you create and save the new queries be certain to use the “qry” perfix as a part of the naming convention. Also consult your instructor for instruction about submitting results.

1. Start access and open Hudson.accdb database from STS folder.

2. Kim wants to identify employees who live in the same neighborhood in Edmonton so they can create car pools and share rides to work. In Edmonton, the postal codes roughly correspond to neighborhood. Prepare a list of employees who live in the same neighborhood so that Kim can create a list of employees who can share rides to work. Name the query qryDuplicatePostalCodes.

3. A new policy at Hudson Bay Pharmacy is that all employees must acquire and maintain certifications in adult, infant, and child CPR and in using defibrillators. Kim asks you to identify employees who have not completed any certification training. Save the query as qryNoTraining.

4. Kim also needs to list all employees and the classes they have taken. The results should include current employees who have not attended training as well as those who have. Save the query as qryEmployeeTraining.

5. Kim also needs to identify employees whose CPR or defibrillators certification has expired, depending on the time period she specifies. Show all employees whose Adult CPR, Child/Infant CPR, or Defibrillators Use certification has expired in any specified time period. Save the query as qryUpToDate.

6. Mai Yan, manager of Hudson Bay Pharmacy, wants to identify the five current nonsalaried employees who are earning the highest wages per hour. These are the five employees who have been working for the pharmacy the longest or who have regularly received raises for their work. List the top five wage earners of all the current nonsalaried employees. Save the query as qryTop5HourlyRates.

7. To prepare for employee reviews, Kim needs to calculate the minimum, maximum, and average hourly rates for each job category. Provide this information for her, saving the query as qryMaxMinAvgHourlyRate.

8. Mai is considering offering life insurance as an employee benefit, and needs to know the current age of all employees. Provide this information for her. Be certain to provide an appropriate name for the column with the result and to show the ages in descending order. Include the job title and format the results so that they include one decimal place. Also show the first name and last name together. Save the query as qryEmployeeAge.

9. Kim asks you to provide one other statistical analysis. Show the average age of employees by job title. Save this query as qryAvgEmployeeAge.

10. Close the Hudson.accdb database and Access.