KB199137 - ACC2000: Access C/S Does Not Display Full Results with COMPUTE

Article ID: 199137

Article Last Modified on 6/29/2004

Microsoft Access 2000 Standard Edition

This article was previously published under Q199137

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SYMPTOMS

When you run a stored procedure that contains COMPUTE / COMPUTE BY, in a Microsoft Access project, the results are truncated.

RESOLUTION

To display sums, use a Microsoft Access report instead.

For additional information about using sums in reports, please see the following articles in the Microsoft Knowledge Base:

208850 ACC2000: How to Sum a Calculation in a Report

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

In Microsoft SQL Server Query Analyzer, run the following SQL in the sample database Northwind. This code creates the MyCompute stored procedure:
CREATE Procedure MyCompute
AS
SELECT customerid, freight
FROM orders
WHERE customerid LIKE 'AN%'
ORDER BY customerid, freight
COMPUTE SUM(freight) by customerid
COMPUTE SUM(freight)

In Query Analyzer, run the following statement:

EXEC MyCompute

Note that the output pane shows the following:

   customerid freight               
   ---------- --------------------- 
   ANATR      1.6100
   ANATR      11.9900
   ANATR      39.9200
   ANATR      43.9000
   
              sum
              ===================
              97.42
   
   ANTON      4.0300
   ANTON      15.6400
   ANTON      22.0000
   ANTON      36.1300
   ANTON      47.4500
   ANTON      58.4300
   ANTON      84.8400
   
              sum
              ===================
              268.52
   
              sum
              ===================
              365.94
   
(14 row(s) affected)

Open a project connected to the Northwind database.

Run MyCompute. Note that in the output pane, you only see the following:

   customerid    freight
   ---------------------
   ANATR         $1.61
   ANATR         $11.99
   ANATR         $39.92
   ANATR         $43.90

Note that nearly two thirds of the information displayed in Query Analyzer does not show in the Microsoft Access project.

Additional query words: pra Microsoft Access Client Server

Keywords: kbbug kbclientserver kbpending KB199137