Access Project 2

XYZ Fitness has many fitness clubs around southern California. They maintain a database of 4 tables: (1) the Employees table keeps the records of the basic personal information of the employees, (2) the Clubs table keeps the records of the locations of the clubs the employees work in and their positions, (3) the Pay table keeps the records of the hourly pay rates of the workers, and (4) the Hours table keeps the records of the numbers of work hours of the employees accumulate in different weeks.

Step 1: Download this zipped sample database to your folder and extract it.

Step 2: Browse the fields (design view) and data (data sheet view) of the tables in the database.

Step 3: Creating Queries

Create queries using the Wizard (for Queries objects) to retrieve from the relevant tables the information described in the following, and name the queries as noted.

  1. Query Employee 1: Find all employees who were born before 12/31/1973 and hired before 12/31/2001. Show the first name, last name, birthday, and hire date.
  2. Query Employee 2: Find all female employees who were either born after 12/31/1973 or hired before 12/31/2001. Show the first name, last name, birthday, and city.
  3. Query Employee 4: Find all employees who hold the positions of Club Director or Club Manager. Show the employee ID, first name, last name, position, and location.
  4. Query Employee 5: Find all employees who work at least 40 hours in the week ending in 6/14/2005. Show the employee ID, position, first name, and last name.
  5. Query Employee 6: Find all employees who are fitness instructors and work at least 10 hours in the week ending in 6/14/2005. Show the employee ID, position, location, first name, and last name.
  6. Query Employee 7: Find all employees who were either (i) born after 12/31/1973 with a pay rate of at least 12.00 or (ii) born before 1/1/1974 with a pay rate of at least 18.00 Show the employee ID, first name, last name, birthday, and pay rate.

Step 4: Test your queries to see whether they can correctly retrieve the information.

Step 5: Report in your weekly progress report the number of records retrieved by queries #1-#6 respectively.