Microsoft KB Archive/63523

Works: Capturing Report Summary Totals into a New Database

PSS ID Number: Q63523 Article last modified on 06-12-1996

1.00 1.05 2.00

MS-DOS

= SUMMARY =

A report in Works can be created that is sorted by a summary total in one of the summary break fields. A practical example is a list of names, departments, and salaries. The final result desired is a listing of departments sorted by the amount of salary paid to each. For example:

Sales $500,000 Production $430,000 Shipping $350,000 Clerical $300,000

Note: The following procedure has one major limitation in that the report created has no record lines; it will consist of break field summary totals only.

The important field names in the database are a person’s name, department(dept), and salary. The original report is constructed by sorting and breaking on the department field, as shown in the following:

  View a new report (ALT, V, N in Works 2.00; ALT, R, N in Works 1.00 and 1.05.   Sort the records based on department (ALT, S, O in Works 2.00; ALT, Q, S Works 1.00 and 1.05).  Type “Dept” in the first field. Choose Ascending or Descending. Place an X in the Break field (ALT+G). 

A row will be inserted in the database that says Summ Dept. The following are two important formulas that must be included in this row:


 * 1) Summ(Salary) – This formula should be automatically entered when you perform the sort.
 * 2) =Department – This formula will print out the name of the department next to the summary totals. For best results, place this formula one cell to the left of summ(salary).

All other formulas can be deleted or left alone depending on your preference. The next step is to create a new database file where the summary totals will be carried over, as shown in the following:

  Create a new database file with the fields dept and salary (and any other fields that may be carried over from the report). Note that the order in which the fields are entered should parallel the order in which their corresponding values appear in the summary line of the report already created. </li>  Change to list view (ALT, V, L in Works 2.00; ALT, O, V Works 1.05 and 1.00.) </li>  Move back to the report file by going to the Window menu (ALT, W) and choosing the number that corresponds to the proper filename. </li>  Highlight the entries made in the summ(dept) row. </li>  Choose Edit Copy (ALT, E, C in Works 1.00, 1.05, and 2.00). </li>  Move back to the new file by going to the Window menu (ALT, W) and choosing the number that corresponds to the proper filename. </li>  Press ENTER. </li>  The Summary information from the report has been moved to the new database. </li>  The final step is to sort this new database based on salary (ALT, S, O in Works 2.00; ALT, Q, S in Works 1.05 and 1.00). Choose Salary in first field and press ENTER. You now have sorted summary totals. </li></ol>

KBCategory: kbprint KBSubcategory: dworkskb Additional reference words:

=
================================================================ Copyright Microsoft Corporation 1996.