Using QMF

Displaying data from more than one table

With QMF you can display data from more than one table at a time. You can specify up to 15 tables from which to select data.

For example, suppose that you need salary data for each department within each corporate division.

It is not likely that all of this data is in one table. If you check the sample tables in Appendix B, QMF Sample Tables, you see that department numbers are found in both the Q.ORG and Q.STAFF tables, division and department names are found only in the Q.ORG table, and salary data is found only in the Q.STAFF table.

You need to join the tables to get all the data you need. Joining tables means linking them by columns that contain the same kind of information.

In this example, the DEPT column in the Q.STAFF table and the DEPTNUMB column in the Q.ORG table both contain department numbers. You can join the tables by these two columns.

When you join the Q.STAFF and Q.ORG tables by department number, QMF joins every row in Q.STAFF to every row in Q.ORG that has the same department number. By joining the tables, you produce a report that shows the names of all departments.

To join tables:

1. On separate lines on the Tables panel, enter the names of the tables from which you want to display data. The Join Columns panel displays.

   The columns from each table appear under separate headings.

   Figure 38. The Join Columns panel
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                                       MODIFIED  LINE    1       |
   |                                       +-------------------------------------+  |
   |   Tables:                             |               Tables                |  |
   |     Q.ST +----------------------------------------------------------------+ |  |
   |     Q.OR |                          Join Columns                          | |  |
   |          |                                                                | |  |
   |   Join T | Select a column from each table.  Rows that have equal         | |  |
   |   > ...  | values in those columns will be joined.                        | |  |
   |          |                                                                | |  |
   |   Column | Q.STAFF                          Q.ORG                         | |  |
   |     ALL  |                                                    1 to 7 of 7 | |  |
   |          | 3 1. ID                         1 1. DEPTNUMB                  | |  |
   |   *** EN |   2. NAME                         2. DEPTNAME                  | +  |
   |          |   3. DEPT                         3. MANAGER                   | |  |
   |          |   4. JOB                          4. DIVISION                  | |  |
   |          |   5. YEARS                        5. LOCATION                  | +  |
   |          |   6. SALARY                                                    |    |
   |          |   7. COMM                                                      |    |
   |          +----------------------------------------------------------------+    |
   |          | F1=Help  F5=Describe  F7=Backward  F8=Forward  F12=Cancel      |    |
   |          +----------------------------------------------------------------+    |
   |                                                                                |
   +--------------------------------------------------------------------------------+

2. Select the column you want from the first table. For this example, type 3 to select the DEPT column from the Q.STAFF table.
3. Select the column you want from the second table. For this example, type 1 to select the DEPTNUMB column from the Q.ORG table.
4. Press Enter. The columns you used to join the tables are displayed in the echo area. The Specify panel displays, with choice 2, Columns, selected for you.

   Figure 39. QMF displays the names of the tables you selected to join.
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                                       MODIFIED  LINE    1       |
   |                                                    +------------------------+  |
   |   Tables:                                          |        Specify         |  |
   |     Q.STAFF(A)                                     |                        |  |
   |     Q.ORG(B)                                       | Select an item.        |  |
   |                                                    |                        |  |
   |   Join Tables:                                     | 2 1. Tables...         |  |
   |     A.DEPT And B.DEPTNUMB                          |   2. Columns...        |  |
   |                                                    |   3. Row Conditions... |  |
   |   Columns:                                         |   4. Sort...           |  |
   |     ALL                                            |   5. Duplicate Rows... |  |
   |                                                    +------------------------+  |
   |   *** END ***                                      | F1=Help  F12=Cancel    |  |
   |                                                    +------------------------+  |
   |                                                                                |
   +--------------------------------------------------------------------------------+

5. Press Enter to select choice 2, Columns.

   The Columns panel displays with the names of the columns from all the tables you selected.

6. Enter x beside each column you want to display on the report. For this example, select the DEPTNAME column and the DIVISION column. The columns you select to display on the report appear in the echo area. The Specify panel displays, with choice 3, Row Conditions, selected.
7. To get the total salary for each department, use QMF's summary functions. On the Specify panel, select choice 2, Columns. The Columns panel displays.
8. Select choice 2, Summary Functions (SUM, etc.) at the bottom of the panel. The Summary Functions panel displays.
9. Select the summary function. For this example, select Sum of. The Summary Function Items panel displays.
10. Select the column you want the summary function to act on. For this example, select the SALARY column.
11. To finish the query, sort the rows by department name and division. On the Specify panel, select choice 4, Sort. The Sort panel displays.
12. For this example, select Ascending and the DIVISION column.

    Repeat these steps to sort the DEPTNAME column in ascending order.

    Figure 40. QMF displays the finished query.
    

    +--------------------------------------------------------------------------------+
    | PROMPTED QUERY                                       MODIFIED  LINE    1       |
    |                                                                                |
    |   Tables:                                                                      |
    |     Q.STAFF(A)                                                                 |
    |     Q.ORG(B)                                                                   |
    |                                                                                |
    |   Join Tables:                                                                 |
    |     A.DEPT And B.DEPTNUMB                                                      |
    |                                                                                |
    |   Columns:                                                                     |
    |     DEPTNAME                                                                   |
    |     DIVISION                                                                   |
    |     SUM (SALARY)                                                               |
    |                                                                                |
    |   Sort:                                                                        |
    |     Ascending by DIVISION                                                      |
    |     Ascending by DEPTNAME                                                      |
    |                                                                                |
    |   *** END ***                                                                  |
    +--------------------------------------------------------------------------------+

13. Press the Run function key to see the final report, showing the total salary data for each department within each division.

    Figure 41. The report shows salary data for departments within divisions.
    

    +--------------------------------------------------------------------------------+
    |   DEPTNAME        DIVISION                           SUM(SALARY)               |
    |   --------------  ----------  ----------------------------------               |
    |   HEAD OFFICE     CORPORATE                             83463.45               |
    |   MID ATLANTIC    EASTERN                               64286.10               |
    |   NEW ENGLAND     EASTERN                               61929.33               |
    |   SOUTH ATLANTIC  EASTERN                               77285.55               |
    |   GREAT LAKES     MIDWEST                               58369.05               |
    |   PLAINS          MIDWEST                               86090.80               |
    |   MOUNTAIN        WESTERN                               66147.00               |
    |   PACIFIC         WESTERN                               86076.20               |
    +--------------------------------------------------------------------------------+