Advanced human resource data in the Commonwealth's information warehouse

116 Pages·1999·4 MB·English

by  Massachusetts. Human Resources Division. Training Unit

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
[phone] 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