Using ms access to analyse payroll information
Download
1 / 46

using ms access to analyse payroll information - PowerPoint PPT Presentation


  • 233 Views
  • Uploaded on

NWDUG 2001. USING MS ACCESS TO ANALYSE PAYROLL INFORMATION. The Budget Officer’s Perspective of HR/Payroll Records. PRESENTATION. NWDUG 2001. 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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'using ms access to analyse payroll information' - Jims


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Using ms access to analyse payroll information l.jpg

NWDUG 2001

USING MS ACCESS TO ANALYSE PAYROLL INFORMATION

The Budget Officer’s Perspective of HR/Payroll Records


Presentation l.jpg
PRESENTATION

NWDUG 2001

  • 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 l.jpg
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


Step 1 l.jpg

NWDUG 2001

STEP #1

CREATE NEW ACCESS DATABASE


Create tables with fixed data l.jpg

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

Create Tables with Fixed Data


Sample table object codes l.jpg
Sample Table - Object Codes

NWDUG 2001

OBJECTDESCRIPTION

6110 CUPE

6120 BCGEU

6130 CCFA

6140 Exempt


Step 2 l.jpg

NWDUG 2001

STEP #2

ADD CURRENT COLLEAGUE DATA


Paytodat fields used l.jpg
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


Pull information l.jpg
Pull Information

NWDUG 2001

  • “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 l.jpg
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


Importing text file into access l.jpg
Importing Text File Into Access

NWDUG 2001

File Pulldown -Get External Data - Import


Import text wizard opens file l.jpg

NWDUG 2001

Import Text Wizard Opens File

Click on Advanced


Add field names l.jpg

NWDUG2001

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 l.jpg
Type in Your Field Names

NWDUG 2001

Save your field names using “Save as”


Helpful hint l.jpg

NWDUG 2001

HELPFUL HINT

BRING GROSS AMOUNTS IN AS CURRENCYDATA TYPE TO ALLOW FOR NEGATIVE NUMBERS


New table created l.jpg
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


Step 3 l.jpg

NWDUG 2001

STEP #3

CREATE REFORMATTED TABLE FOR CURRENT DATA


Break apart gl account l.jpg

NWDUG 2001

Break Apart GL Account

  • 106423566110 -Arrives from Colleague

  • Fund = 10

  • Function = 64

  • Unit = 2356

  • Object = 6110


Using make table query l.jpg

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)

Using Make Table Query

NWDUG 2001


Run make table query l.jpg

NWDUG 2001

Run Make Table Query

  • 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


One time only l.jpg

NWDUG 2001

ONE TIME ONLY

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


Step 4 l.jpg

NWDUG 2001

STEP #4

LINK YOUR TABLES TOGETHER IN A QUERY


Creating your query l.jpg

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



Add all fields to your query l.jpg

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 l.jpg

NWDUG 2001

Use Mouse to Add Files

Drag down the fields


Save to name your query l.jpg

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”


Step 5 l.jpg

NWDUG 2001

STEP #5

WRITE SOME SIMPLE REPORTS


Creating your report l.jpg

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


Step 6 l.jpg

NWDUG 2001

STEP #6

DEFINE YOUR CRITERIA TO ANSWER AD HOC NEEDS


Who is paying tom cruise l.jpg

NWDUG 2001

Who is paying Tom Cruise?

  • In your Payroll Records Query, define the criteria for Field Colleague ID as “11278”

  • OR, define the criteria for Field Surname as “Cruise”

  • Run your report.


Enter your search criteria l.jpg

NWDUG 2001

Enter your search criteria

Enter name for that field


Sample report l.jpg
SAMPLE REPORT

NWDUG 2001


Who is being paid from this g l l.jpg

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 report35 l.jpg
SAMPLE REPORT

NWDUG 2001


Step 7 l.jpg

NWDUG 2001

STEP #7

CREATE STANDARD REPORTS FOR END USERS


Who has been paid this year l.jpg

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 report38 l.jpg
SAMPLE REPORT

NWDUG 2001


What is the total fiscal payroll l.jpg

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 report40 l.jpg
SAMPLE REPORT

NWDUG 2001


Mailing reports to departments l.jpg

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)


Helpful hint gotcha l.jpg

NWDUG 2001

HELPFUL HINT (GOTCHA)

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


Epaj entries show corrections l.jpg
EPAJ entries show “corrections”

NWDUG 2001

GL Acct NoAmount

10 1301

10-62-6786-6140 3,001.25-

10 1301

10-61-7190-6140 3,001.25

Must equal zero --> 0.00


Another application l.jpg

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 report45 l.jpg
SAMPLE REPORT

NWDUG 2001



ad