Finding Subtotals with dBASE, Excel, or Text Files

The dBASEFile, TextFile, and ExcelFile drivers for Q+E for Microsoft Excel do not support the COMPUTE BY or GROUP BY clauses required to find subtotals in a SQL query.

The information below describes a workaround using the Data Table command in Microsoft Excel.

More Information:

This example uses EMP.DBF, one of the sample files found in the QE subdirectory in your EXCEL directory. The goal of this example is to find the total salary of employees in each department.

1. Import EMP.DBF into Excel.

   a. Start Excel.

   b. From the File menu, choose Open, select the QE subdirectory, and

         select EMP.DBF.
   

2. Extract the list of departments.

   a. Enter "DEPT" (without the quotation marks) in cell J1.

   b. Select J1:J2.

   c. From the Data menu, choose Set Criteria.

   d. Enter "DEPT" (without the quotation marks) in cell K1.

   e. Select K1.

   f. From the Data menu, choose Extract.

   g. When you get the Paste dialog box, select the Unique Records Only

         check box.
   

3. Set up the data table.

   a. Select cell K1.

   b. From the Edit menu, choose Clear, select All, and then choose

         OK.
   

   c. Select cell L1.

   d. Enter the following:

            =DSUM(Database,"SALARY",Criteria)
   
      e. Select K1:L5.
   
      f. From the Data menu, choose Table.
   
      g. Enter J2 for the Column Input cell and choose OK.
   
      h. The following is the result:
   
                     K        L
            1               412450
            2       D101    169900
            3       D050     68900
            4       D202     86750
            5       D190     86900
   
   

"Microsoft Excel User's Guide," version 3.0, pages 265-268

"Microsoft Excel Reference Guide," version 2.1, pages 153-155