1 / 46

USING MS ACCESS TO ANALYSE PAYROLL INFORMATION

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

Jims
Download Presentation

USING MS ACCESS TO ANALYSE PAYROLL INFORMATION

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NWDUG 2001 USING MS ACCESS TO ANALYSE PAYROLL INFORMATION The Budget Officer’s Perspective of HR/Payroll Records

  2. 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”

  3. 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

  4. NWDUG 2001 STEP #1 CREATE NEW ACCESS DATABASE

  5. 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

  6. Sample Table - Object Codes NWDUG 2001 OBJECTDESCRIPTION 6110 CUPE 6120 BCGEU 6130 CCFA 6140 Exempt

  7. NWDUG 2001 STEP #2 ADD CURRENT COLLEAGUE DATA

  8. 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

  9. 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

  10. 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

  11. Importing Text File Into Access NWDUG 2001 File Pulldown -Get External Data - Import

  12. NWDUG 2001 Import Text Wizard Opens File Click on Advanced

  13. 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

  14. Type in Your Field Names NWDUG 2001 Save your field names using “Save as”

  15. NWDUG 2001 HELPFUL HINT BRING GROSS AMOUNTS IN AS CURRENCYDATA TYPE TO ALLOW FOR NEGATIVE NUMBERS

  16. 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

  17. NWDUG 2001 STEP #3 CREATE REFORMATTED TABLE FOR CURRENT DATA

  18. NWDUG 2001 Break Apart GL Account • 106423566110 -Arrives from Colleague • Fund = 10 • Function = 64 • Unit = 2356 • Object = 6110

  19. 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

  20. 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

  21. NWDUG 2001 ONE TIME ONLY CREATE YOUR MAKE TABLE QUERY ONCE ONLY THEN USE IT EACH TIME YOU IMPORT

  22. NWDUG 2001 STEP #4 LINK YOUR TABLES TOGETHER IN A QUERY

  23. 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

  24. Joining Tables in Access NWDUG 2001

  25. 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”

  26. NWDUG 2001 Use Mouse to Add Files Drag down the fields

  27. 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”

  28. NWDUG 2001 STEP #5 WRITE SOME SIMPLE REPORTS

  29. 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

  30. NWDUG 2001 STEP #6 DEFINE YOUR CRITERIA TO ANSWER AD HOC NEEDS

  31. 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.

  32. NWDUG 2001 Enter your search criteria Enter name for that field

  33. SAMPLE REPORT NWDUG 2001

  34. NWDUG 2001 Who is being paid from this G/L? • In your query, define the criteria for Field UNIT as “1069” • Run your report.

  35. SAMPLE REPORT NWDUG 2001

  36. NWDUG 2001 STEP #7 CREATE STANDARD REPORTS FOR END USERS

  37. 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

  38. SAMPLE REPORT NWDUG 2001

  39. 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

  40. SAMPLE REPORT NWDUG 2001

  41. 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)

  42. NWDUG 2001 HELPFUL HINT (GOTCHA) Journel Entries in CF must be matched with EPAJ entries in Payroll to maintain accuracy

  43. 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

  44. 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

  45. SAMPLE REPORT NWDUG 2001

  46. Questions?

More Related