Table Of Content

HKP JV\AS?. !,£.: /-\y "is Human Advanced Human Resource Data Resources in Division the Commonwealth's Training Unit Information Ashburton 1 Warehouse Place th 10 Floor Room 1002 MA Boston, S&S On,] ^ch (617)727-3777 De Posi: Use M Its Extension 714 Created On: January 1 1, 1999 1 Advanced Human Resource Data in the Commonwealth's Information Warehouse Page 1 - Part Introduction I Statement of Course Purpose page 4 Course Objectives page 4 Sources of HR Data page 7 Other Data Sources page 8 Standard Measures in HR Data page 9 HR Three Types of Tables page 10 HR Data Models page 1 Building a Query page 14 Using equal, left outer and right outer joins page 19 Part - Using Advanced Microsoft Access Features via HR Data II Understanding query operators page 23 Expression Builder page 24 Summary Calculations page 25 Creating a Make Table page 26 Creating an Append Query page 27 Creating a Parameter Query page 28 Creating Reports via the Report Wizard page 29 Part III - Building "Customized" Queries Working with multi-table queries Sorting data in Microsoft Access Using various criteria selections within HR Data table - Part IV Building "Specialized" Queries Based on questions of general interest Based on questions specific to one's department Based on questions specific to one's own job function Analyzing the data received for accuracy and practicality Using the data dictionary on-line s:\hrd\training\paris\advintro.doc Advanced Human Resource Data in the Commonwealth's Information Warehouse Page 2 APPENDICES Layout ofSample Advanced Queries Questions based on HR Data Tables 1. Which employees are currently out on education or maternity leave in my bargaining unit? 2. How many employees in my department are classified as LTHT (less than half-time) status? 3. Which employees in my department on out on any type of leave (other than "Reg")? 4. What is the next step date for employees in my department with last names beginning A-F? 5. What are the names of those people in my department who have an FTE status between .4 and .9? 6. Which bargaining units currently exist in my department? 6A. Which employees in my department are assigned to my own bargaining unit? 7. Which employees in my department have a next step date within this current calendar year? s:\hrd\training\paris\advintro doc Advanced Human Resource Data in the Commonwealth's Information Warehouse Page 3 Layout ofSample Advanced Queries Questions based on the PCRS (Labor History Table) 1. How many hours personal and sick time were used in my department between 9/1/98 and 12/31/98? 2. Which employees in my department have used greater than (15) hours of vacation between 9/1/98 and 12/31/98? 3. How many hours of vacation time did my entire department use between 5/1/98 and 8/31/98? 4. Which employees in my department have used greater than (15) hours of sick time within the past month? s:\hrd\training\paris\advintro.doc . Advanced Human Resource Data in the Commonwealth's Information Warehouse Page 4 Statement of Course Purpose and Objectives Course Purpose : The purpose of this course is to further enhance the previous acquired knowledge of users of the Human Resource Data in the Commonwealth's Information Warehouse. Such enhancements will include the ability to query the information warehouse and receive valid and specific information to a wide range of human resource and payroll-related topics. As a result of this session you will be able to : 1 Demonstrate the use of advanced Microsoft Access functions in the HR Data in the Information Warehouse. 2. Analyze the data received for accuracy and practicality. 3. Create and design reports based on data retrieved from HR Data in the Warehouse. 4. Analyze data results to Microsoft Excel and Microsoft Word. 5. Learn how to obtain valuable answers to question concerning your department and your own job function from the HR Data in the Warehouse. 6. Effectively use the on-line data dictionary tables to research element and table names. s:\hrd\training\paris\advintro.doc