Query Lesson #2: Query to Excel to Access
  1. Open Up PeopleSoft PeopleTools
  2. Go,PeopleTools,Query
  3. File Open , Click on Name (a list of queries opens up)
  4. Scroll down and select XRFFLPN; double-click to open
  5. File, Save as, click private, desired file (query) name
  6. Click the Criteria tab
  7. Click & Drag "Field name" to right hand side of screen
  8. Select the Operator "Less than" to the left of the Expression
  9. Double click the Expression 2 column next the Field Name (looks like ' ')
  10. In the constant box,Type in NAAAAAAAAAAAAAA. Retrieves fieldnames A-M (50406 records. (Type one N and 17 A’s to fill in A’s to the end of the available space)
  11. Push "X" Excel button in top right of screen to download info to excel
  12. Select Enable Macros if need be
  13. Select enable macros again if need be
  14. Save in Excel as file type Microsoft Excel 97 or 2000 with the file name of your choice
  15. Return to Query;
  16. Change Operator to "Greater Than"
  17. Change "Expression 2" to MZZZZZZZZZZZZZ. Retrieves fieldnames N-Z (38496 records). (Type one M and 17 Z’s to fill in Z’s to the end of the available space)
  18. Run to Excel and save as a different file name than the first file
  19. Close Query
  20. Open Microsoft Access and create a new database
  21. Go to the Tables tab
  22. Click on File, Get External Data, Import and select one of the two files that you previously saved.
  23. A spreadsheet wizard will pop up, click First Row Contains Column Heading, Next, Next, Next, No Primary Key, Next, Finish, ok
  24. Repeat # 22 & 23 with the 2nd file that you saved in Excel.
  25. Save with a different table name than the table saved in steps 22 & 23.
  26. Go to the Query Tab in Access
  27. Click New, Click one of the two tables that you imported , click Add
  28. Highlight all fields, then drag to the bottom portion of the query screen. The fields should show up in the bottom half of the screen.
  29. Click on Query, Make Table Query, and call it "Table 1"
  30. Click on the footprint to run the information to a new table to be called "Table 1"
  31. Exit out of this query. (Do not save.)
  32. Click New, Click the other table that you imported , click Add
  33. Highlight all fields, then drag to the bottom portion of the query screen. The fields should show up in the bottom half of the screen.
  34. Click on Query, Append Query, and select "Table 1"
  35. Click on the footprint to append the information to a "Table 1"
  36. You now have a complete new table (Table 1) of all information from your two tables. (Should have 88,902 records)
  37. Query and report away!!!

Query Lesson 2: EFFECTIVE DATING

IN PSOFT QUERY

FIND FUND_TBL AND DOUBLE CLICK

SELECT NO EFFECTIVE DATE OPTION

RECORD MOVES TO QUERY TAB

DOUBLE CLICK ON FOLLOWING FIELDS TO SELECT:

FUND_CODE

EFFDT

EFF_STATUS

DESCR

RUN QUERY - VIEW RESULTS – NOTE EFFECTIVE DATES

SELECT CRITERIA TAB

CLICK AND DRAG EFFDT FROM LEFT BOX TO RIGHT BOX

Note: In order to display a variety of effetive dates, do not select Business Unit.

SELECT OPERATOR EQUAL TO

RIGHT CLICK IN EXPRESSION 2 BOX

If expression is checked, date should be entered to match output. (YYYY-MM-DD)

If constant is checked, date should be entered to match Windows system settings.

-OR- DOUBLE CLICK IN BOX BELOW EXPRESSION 2 HEADING

EDIT CONSTANT VALUE BY TYPING 02061997 IN BOX

CLICK OK (OR ENTER)

RUN QUERY – VIEW RESULTS

ONLY ONE ROW RETURNED WITH

RETURN TO CRITERIA TAB

RIGHT CLICK IN BOX BELOW EXPRESSION 2 HEADING

SELECT EXPRESSION

TYPE 1998-07-01

CLICK OK (OR ENTER)

RUN QUERY – VIEW RESULTS

RETURNS ROWS WITH 1998-07-01 DATE