Creating a Query


Concept


Steps:

  1. Begin by navigating to the Records page.

     

    Click the Reporting Tools link.

    Step 1
  2. Click the Query Manager link.

    Step 2
  3. Click the Create New Query link.

    Step 3
  4. Use the Records page to select the records upon which to base the new query. Select the criteria to use when searching for records. You can search for existing records by entering appropriate keywords.

    Step 4
  5. The first step in creating a query is to open an existing record on which you want to base the query. You want to create a query about student degrees, but are not certain of how the record name is stored in the database. You know the record name contains the letters DEG, so you can do an advanced search to locate records containing those letters.

     

    Click the Advanced Search link.

    Step 5
  6. You need to change the search operator for the Record Name field.

     

    Click the Record Name list.

    Step 6
  7. Change the operator to contains.

     

    Click the contains list item.

    Step 7
  8. Click in the Record Name field.

    Step 8
  9. Enter the desired information into the Record Name field. Enter

     a valid value
     e.g.
     "DEG"
    .

    Step 9
  10. Click the Search button.

    Step 10
  11. The search results display all the records that contain the letters DEG. Use the ACAD_DEGR - Student Degree Table record to create the query.

    Step 11
  12. Click the Add Record link.

    Step 12
  13. The Query page appears, displaying several fields. Use this page to add fields to a query. Add the fields, EMPLID, DEGREE, INSTITUTION, ACAD_CAREER, DEGR_CONFER_DT, and ACAD_DEGR_STATUS to the query.

    Step 13
  14. Click the EMPLID - EmplID option.

    Step 14
  15. Click the DEGREE - Degree option.

    Step 15
  16. Click the INSTITUTION - Academic Inst... option.

    Step 16
  17. Click the ACAD_CAREER - Academic Career option.

    Step 17
  18. Click the DEGR_CONFER_DT - Confer Date option.

    Step 18
  19. Click the ACAD_DEGR_STATUS - Academic ... option.

    Step 19
  20. Click the Fields tab.

    Step 20
  21. Use the Fields page to view the fields that you selected.

    Step 21
  22. In the Record.Fieldname column, notice the letter A before each field name. This letter is an alias that represents the table from which this field has been extracted.

    Step 22
  23. You can change the order of the columns that the fields are displayed in.

     

    Click the Reorder / Sort button.

    Step 23
  24. Use the Edit Field Ordering page to reorder the columns.

    Step 24
  25. You want the Academic Degree Status field to appear before the Confer Date field in your report. Currently the Confer Date field appears at the fifth position.

     

    Click in the New Column field.

    Step 25
  26. Enter the desired information into the New Column field. Enter

     a valid value
     e.g.
     "5"
    .

    Step 26
  27. Click the OK button.

    Step 27
  28. Notice that the Academic Degree Status field now appears before the Confer Date field.

     

    Next, use the Edit button to change attributes of the Confer Date field.

     

    Click the Edit button.

    Step 28
  29. Use the Edit Field Properties page to customize your field properties.

    Step 29
  30. You need to change the column heading for the Confer Dt field to Degree Confer Date.

     

    Click the Text option.

    Step 30
  31. Click in the Heading Text field.

    Step 31
  32. Enter the desired information into the Heading Text field. Enter

     a valid value
     e.g.
     "Degree Confer Date"
    .

    Step 32
  33. Click the OK button.

    Step 33
  34. Notice the new heading text for the Confer Date field.

     

    Click the Save button.

    Step 34
  35. Use the Enter a name to save this query: page to specify a name and description for the new query you created.

    Step 35
  36. Enter the desired information into the Query field. Enter

     a valid value
     e.g.
     "DEGREES"
    .

    Step 36
  37. Click in the Description field.

    Step 37
  38. Enter the desired information into the Description field. Enter

     a valid value
     e.g.
     "General info about degrees"
    .

    Step 38
  39. Use the Query Type field to specify the type of query as User, Process, or Role. Standard queries are defined as User types, and queries that use workflow are defined as Process or Role types. For the exercise, retain the default query type.

    Step 39
  40. You can specify the query as either Private or Public by selecting an entry in the Owner field. A Private query can be accessed and modified by only the user who created the query. However, any user who has access to the query records can run, modify, or delete a Public query. For this exercise, retain the default values.

    Step 40
  41. Click the OK button.

    Step 41
  42. Finally, view the results of the query.

     

    Click the Run tab.

    Step 42
  43. Use the Run page to preview the query you have just created.

    Step 43
  44. Click the View SQL tab.

    Step 44
  45. Use the View SQL page review the SQL statement equivalent to the query that you created.

    Step 45

You successfully created a query by using Query Manager.

 

Creating your own queries enables you to select the table or tables from which you want to execute a query and to design the fields within those tables so that only the data you want displays.

Table of Contents  Start Topic