NWDUG 2001

USING MS ACCESS TO ANALYSE PAYROLL INFORMATION

The Budget Officer’s Perspective of HR/Payroll Records

NWDUG 2001

PRESENTATION

• • • One application of a reporting technique with step-by-step instructions Can be used any time you want to move Colleague information into MS Access This is a “Colleague” Report, presentation only comes from “Access”

PROJECT OUTLINE

NWDUG 2001

• • • • Extract Colleague Data from PayToDat File Import file into MS Access Database Reformat Information for more flexibility Use standard “Querys” and “Reports” to answer Ad Hoc Requests • Provide End-Users with Easy to Read Reports

NWDUG 2001

STEP #1

CREATE NEW ACCESS DATABASE

NWDUG 2001

Create Tables with Fixed Data

• Cost Centre Table • Object Code Table • Function Table • Fields: Fund, Function, Unit, Name, Budget Officer • Fields: Object Code, Description • Fields: Function Code, Description, Division

NWDUG 2001

Sample Table - Object Codes

OBJECT 6110 6120 6130 6140 DESCRIPTION CUPE BCGEU CCFA Exempt

NWDUG 2001

STEP #2

ADD CURRENT COLLEAGUE DATA

PayToDat Fields Used

NWDUG 2001

• • • • • • • GL Acct Number - PTD.GL.NOS

Gross Amount - PTD.AMOUNTS

Colleague ID - PTD.EMPLOYEE.ID

Pay Period End Date - PTD.PERIOD.DATE

Last Name - PTD.LAST

Pay Cycle (PAYCLASS) PCLS.PAYCYCLE Mail Name - S.MIO.MAIL.LABEL

NWDUG 2001

Pull Information

• • • • “Extract” written by IT to create file We limit the amount of data by keying in a pay period end date to start from The file is “FTP’d” to our desktop using QVT The file is labelled “.txt” then imported into MS Access

Import Methods

NWDUG 2001

• • • FAST - Open file in Excel first and “clean it up” - Then Import into Access FASTER- Change to .txt file name and Import into Access using “saved” specs SLICK - Write a macro which calls up file and imports using the “saved” specs

NWDUG 2001

Importing Text File Into Access

File Pulldown -Get External Data - Import

NWDUG 2001

Import Text Wizard Opens File

Click on Advanced

NWDUG 2001

Add Field Names

• • • Not necessary to use the Colleague Names Use names that are logical For example, we use “General Ledger” for PTD.GL.NOS

Type in Your Field Names

NWDUG 2001

Save your field names using “Save as”

NWDUG 2001

HELPFUL HINT

BRING GROSS AMOUNTS IN AS CURRENCY

DATA TYPE

TO ALLOW FOR NEGATIVE NUMBERS

New Table Created

NWDUG 2001

• • Colleague data now in a new Access Table One time only setup required to create your

saved specs

if you use Text method • Each time you refresh your data from Colleague the old table is overwritten

NWDUG 2001

STEP #3

CREATE REFORMATTED TABLE FOR CURRENT DATA

Break Apart GL Account

NWDUG 2001

• • • • • 106423566110 -Arrives from Colleague Fund = 10 Function = 64 Unit = 2356 Object = 6110

Using Make Table Query

NWDUG 2001

• Define Fund • • Define Function Define Unit • Define Object • • • • fund: Mid([general ledger],1,2) function: Mid([general ledger],3,2) unit: Mid([general ledger],5,4) object: Mid([general ledger],9,4)

Run Make Table Query

NWDUG 2001

• • • Use all the fields you brought in from Colleague • Add the new fields you create to reformat the G/L Account Number Call the new table a different name Run this Query each time you refresh Colleague Data

(!) button

NWDUG 2001

ONE TIME ONLY

CREATE YOUR MAKE TABLE QUERY ONCE ONLY THEN USE IT EACH TIME YOU IMPORT

NWDUG 2001

STEP #4

LINK YOUR TABLES TOGETHER IN A QUERY

NWDUG 2001

Creating Your Query

• • Use NEW/”Simple Query Wizard” to link your tables and identify all the fields you’d like to query OR Use DESIGN/Query drop down box and add your tables and join them together using click/drag then identify all the fields you’d like to query

Joining Tables in Access

NWDUG 2001

NWDUG 2001

Add All Fields to Your Query

• • • Add all the information you have to your new query EG Object from the table you made and Object Description from the Object Table “6110” “CUPE”

Use Mouse to Add Files

Drag down the fields

NWDUG 2001

NWDUG 2001

Save to Name Your Query

• • This will be the query you use to answer all your Ad Hoc Requests For Example, call it “Payroll Records”

NWDUG 2001

STEP #5

WRITE SOME SIMPLE REPORTS

NWDUG 2001

Creating Your Report

• • Use NEW/”Report Wizard” to use your Query and identify all the fields you’d like to report and where to sort and total AND/OR Use DESIGN /”Design View” and create your report from your query

NWDUG 2001

STEP #6

DEFINE YOUR CRITERIA TO ANSWER AD HOC NEEDS

Who is paying Tom Cruise?

NWDUG 2001

• In your Payroll Records Query, define the criteria for Field

Colleague ID

as “11278” • OR, define the criteria for Field

Surname

“Cruise” as • Run your report.

Enter your search criteria

NWDUG 2001

Enter name for that field

SAMPLE REPORT

NWDUG 2001

NWDUG 2001

Who is being paid from this G/L?

• • In your query, define the criteria for Field UNIT as “1069” Run your report.

SAMPLE REPORT

NWDUG 2001

NWDUG 2001

STEP #7

CREATE STANDARD REPORTS FOR END USERS

NWDUG 2001

Who has been paid this year?

• • • • • No criteria is necessary, show all records Create a new report by copying the 1st one Sort by Function/Schools Remove the details from your report Show summaries only

SAMPLE REPORT

NWDUG 2001

NWDUG 2001

What is the Total Fiscal Payroll?

• • • No criteria is necessary, show all records Create a new report using Report Wizard Sort by Fund, Function or Object to show various looks at the data • Show summaries only

SAMPLE REPORT

NWDUG 2001

NWDUG 2001

Mailing Reports to Departments

• • • • Put your selection criteria in your query Run the report to check File/Save as “Rich Text Format” (.rtf) E-mail to Department (attachment)

NWDUG 2001

HELPFUL HINT (GOTCHA)

Journel Entries in CF must be matched with EPAJ entries in Payroll to maintain accuracy

NWDUG 2001

EPAJ entries show “corrections”

GL Acct No 10 1301 10-62-6786-6140 10 1301 10-61-7190-6140

Must equal zero -->

Amount 3,001.25 3,001.25

0.00

NWDUG 2001

Another Application

• • • • • Same Technique Y-T-D Actuals - Same as GLBR IT Staff wrote Extract for us Simple to Produce - Easy to Read Colleague Report - Access Presentation

SAMPLE REPORT

NWDUG 2001

Questions?