Format A Query


In this topic you will learn how to format the query results to change the display. This is accomplished on the Fields page. The following are the format options available:

· Change the column heading text.

· Sort the output results by a particular field.

· Change the order of the columns (i.e. the order in which the fields appear in the query results).

· Change the translate code when this option is available for a field.

 

In this example the query results will be formatted as follows:

· Change the heading text to reflect the long description for Highest Education Level.

· Sort the data by Highest Education Level in descending order.

· Change the order of the Highest Education Level field to display as the first column.

· Display the long description of each person's Highest Education Level

rather than the default code.


Steps:

  1. Click the Edit button for the Highest Education Level field.

     

    Note: Fields are formatted one at a time.

    Step 1

  2. In the Heading box:

    · The RFT Short option will display the default selection (Hi

    Educ Lv) in the column heading.

    · The RFT Long option will display the long description

    (Highest Education Level) in the column heading.

    · The Heading Text option will display the free text

    (that you type) in the column heading.

    Step 2

  3. Enter the desired information into the Heading Text field. Enter

     a valid value
     e.g.
     "Highest Education Level"
    .

    Step 3

  4. The Translate Value box will not appear on this page if a field does not have an associated Translate Value.

     

    If a field has an associated Translate Value, the XLT column will contain one of the following three values:

    · N (None) option will display the default translate code

    (e.g. "G" for a Bachelor's Degree) in the query results.

    · S (Short) option will display a maximum of 10 characters (e.g.

    Bachelor's) in the query results.

    · L (Long) option will display a maximum of 30 characters

    (e.g. G-Bachelor's Level Degree) in the query results.

    Step 4

  5. For Translate Value, click the Long option to allow the full description of each person's highest education level to display (instead of abbreviation code).

    Step 5

  6. Click the OK button.

    Step 6

  7. You can see on the Fields page the changes you made to the field properties for HIGHEST_EDUC_LVL.

    Step 7

  8. Reorder/Sort:

     

    Click the Reorder / Sort button.

    Step 8

  9. The Edit Field Ordering page allows you to view and change the Sort Order and Output Order of the fields in a single page.

     

    In this example, the query results will be changed as follows:

    · The query results will be sorted by Highest Education Level first and

    then by Name.

    · The fields (columns) will appear in the following order: Highest

    Education Level, EmplID, Name, Department and Original Hire Date.

    Step 9

  10. Click the Descending option for Highest Education Level.

     

    This option allows the results to sort the Highest Education Level first.

    Step 10

  11. Enter "1" in the New Order By column to display the Highest Education Level as the heading in the first column of the query results.

    Step 11

  12. Enter "4" into the New Column field for Highest Education Level.

     

    This field will now be the fourth column displayed in the query results.

    Step 12

  13. Enter "5" into the New Column field for ORIG_HIRE_DT..

     

    This field will now be the fifth column displayed in the query results.

    Step 13

  14.  Enter "2" into the New Order By field for Name.

     

    The query results will now be sorted by HIGHEST_EDUC_LVL in descending order first and then by NAME in ascending (A-Z) order.

    Step 14

  15. Click the OK button.

    Step 15

  16. Click the Run tab to view the new order of the fields, sort order, and column headings.

    Step 16

  17. Click the Fields tab.

    Step 17

  18. Using the Distinct Option:

     

    Note: When running a query, occasionally the same row of output may be listed more than once when it meets multiple query requirements. Using the Distinct option removes duplicate rows of data. However, the end-user should note that this option may remove pertinent duplicate information, depending on the query. Therefore, if the end-user is not sure if any pertinent data will be removed by selecting this option, the safer alternative would be to run the query results to Excel and manipulate the data in the spreadsheet.

     

    If the end-user is confident that pertinent data will not be removed, the Distinct option is located in the Query Properties box.

     

    Click the Properties link.

    Step 18

  19. Click the Distinct option.

    Step 19

  20. Click the OK button.

    Step 20

  21. Click the Save button.

     

    Note: When you run the query again, any duplicate rows will be deleted.

    Step 21

This completes Format Query.

Table of Contents  Start Topic