HP 3000 Manuals HP 3000 Manuals
F : View Table: Redefining a Compound Data Item Compound data items in IMAGE datasets cannot be accessed by PC users using Access PC. To make compound item information accessible, you can include the compound item in an IMAGE table, then use a view table to resolve it into individual items. The dataset SALES-STAFF in the database SAMPL1 includes a compound item, SALES. This item is made of 12 "buckets" that hold sales figures for each of the 12 months in a year. Define the view table COMPOUND-ITEM to resolve this compound item and calculate the average and total sales per month for each employee.Figure 5-9. View Table COMPOUND-ITEM Computes Averages and Totals You'll learn how to: * Resolve a compound data item into items accessible through Access PC. * Compute the average and total value of a compound item. _________________________________________________________ | | | Reference in this Learning manual: | | Appendix A, Schema for SAMPL1 Database | | | | References in Information Access Server: Database | | Administration: | | Chapter 5, Configuring Tables: | | Adding a View Table Definition | | Appendix B, Constructing View Tables | | Appendix C, View Table Syntax | _________________________________________________________ The definition of view table COMPOUND-ITEM will require these items from the IMAGE table SALES-STAFF: ---------------------------------------------------------------------------------------------- | | | | | Database | Table | Items | | | | | ---------------------------------------------------------------------------------------------- | | | | | SAMPL1 | SALES-STAFF | | | | (IMAGE table) | EMP-NBR | | | | SALES-REP | | | | OFFICE | | | | SALES * | | | | | ---------------------------------------------------------------------------------------------- * Add this item to the IMAGE table as the first step in the following exercise. Adding an Item to the IMAGE TableGo to screen CT --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | In order to resolve the item SALES, you | | | must define it as part of an IMAGE table. | | | Change the IMAGE table SALES-STAFF to | | | include this item. | | | | | | F-1. Go to the Change Table (CT) screen | | | and type SALES-STAFF Enter. | | | | | | F-2. Press Enter to get the items and add | | | SALES to the list of items selected. | | | | | | F-3. Press Enter. | The IMAGE Data Conversion screen appears. | | | | --------------------------------------------------------------------------------------------- | | | | F-4. Replace the default decimal value for | SALES-STAFF now includes the additional | | SALES with a 2 and press Enter. | items you need for this view table | | | definition. | | | | | | (If a user accesses the table SALES-STAFF, | | | the compound item does not appear as a | | | column but the rest of the table can be | | | viewed.) | | | | --------------------------------------------------------------------------------------------- Which View Table Items?Go to screen AVT --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | F-5. Go to the Add View Table (AVT) | | | screen, name this table COMPOUND-ITEM, and | | | give it a description (optional). | | | | | --------------------------------------------------------------------------------------------- | | | | You want to define a view table that | | | resolves the individual items in SALES and | | | calculates the average and total sales | | | dollars. | | | | | | A compound data item can be resolved into | | | individual items in the Item Clause of a | | | view table. Also, the ROW functions can | | | produce any of six new values: the | | | average, standard deviation, sum, minimum, | | | maximum, and count of values in the | | | compound item's "buckets." | | | | | | F-6. In the Item Clause, type the text | | | shown on the next page. These items will | | | provide the following columns in the view | | | table: | | | | | | The first three items will be columns for | | | employee number, name, and office. | | | | | | The next group of items defines as | | | individual items each of the 12 months that | | | make up the compound item SALES. | | | | | | The last two items use two ROW functions. | | | One defines a new item that averages the | | | values in SALES. The other defines an item | | | that totals those values. | | | | | --------------------------------------------------------------------------------------------- ________________________________________________________________________________________ | | | | | Item Clause (separated by commas) | | EMP-NBR, | | NAME = SALES-REP, | | OFFICE, | | JAN = SALES[1], FEB = SALES[2], MAR = SALES[3], APR = SALES[4], | | MAY = SALES[5], JUN = SALES[6], JUL = SALES[7], AUG = SALES[8], | | SEP = SALES[9], OCT = SALES[10], NOV = SALES[11], DEC = SALES[12], | | AVG-SALES = ROWAVG (SALES), | | TOT-SALES = ROWSUM (SALES) | | | | | | | | | | | | | | | ________________________________________________________________________________________ Which View Table Records? --------------------------------------------------------------------------------------------- | | | | What You Do | What Happens | | | | --------------------------------------------------------------------------------------------- | | | | F-7. Type SALES-STAFF in the Using Clause | | | and EMP-NBR in the Sort Clause. | | | | | --------------------------------------------------------------------------------------------- | | | | F-8. Press Enter. | This table will include, for each employee, | | | a record containing sales figures for each | | | of the 12 months, as well as the average | | | and total sales dollars for the year. | | | | | | These records will be listed in ascending | | | order by employee number. | | | | --------------------------------------------------------------------------------------------- | | | | F-9. Choose: | The Add Table Menu appears. From here you | | | can proceed to defining security for your | | Cancel f8 | three new view tables. | | Add | | | | | --------------------------------------------------------------------------------------------- What This Table Will Look Like An Access PC query that chooses columns NAME, JAN, FEB, and AVG-SALES in view table COMPOUND-ITEM will produce this display:Figure 5-10. PC Display of View Table COMPOUND-ITEM Summary In this exercise, you learned how to: * Change an IMAGE table definition in order to add an item. * Define the parts of a compound item as individual items in a view table. * Use two of the ROW functions to compute the total and average of the parts of a compound item.
![[]](../../pic3k/M012040/FFN783c50.gif)
![[]](../../pic3k/M012040/FFN803c50.gif)
![[]](../../pic3k/M012040/FFN813c50.gif)
![[]](../../pic3k/M012040/FFN833c50.gif)