BUG: Bad Result If Multiple Aggregate Functions in SQL Stmt

This article was previously published under Q101553
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
When an SQL query statement contains multiple aggregate functions, theresult set incorrectly contains the same value for all the functions.The result of the first aggregate function is duplicated in the resultcolumn of all of the other functions.
CAUSE
Aggregate functions typically do not contain explicit column names forexpressions in the SQL query. In SQL queries containing multipleaggregate function calls, the Access database layer does not uniquelyidentify the return columns for any functions past the first. Therefore,it duplicates the result column of the first function in the resultcolumns of the succeeding functions

This problem did not occur in Visual Basic version 2.0. In Visual Basicversion 3.0, the Microsoft Access engine was integrated into the dataaccess functionality. The Microsoft Access engine tracks the column byname, whereas Visual Basic version 2.0 tracks the column by the columnoffset.
WORKAROUND
Use aliases for the aggregate functions to solve the problem. Replacethe SQL statement shown below in the "Steps to Reproduce Problem"section with the following SQL statement, which contains the aliasesOne and Two for the column names for the separate SUM expressions:
   Select SUM(PubID) as One, SUM(Au_ID) as Two From Titles				
The Alias names can be anything other than the column name and mustbe unique within the statement.

After inserting the aliases, run the SQL statement again and noticethat the two fields now correctly show the different results.
STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listedat the beginning of this article. We are researching this problem and willpost new information here in the Microsoft Knowledge Base as it becomesavailable.
MORE INFORMATION

Steps to Reproduce Problem

  1. Start Visual Basic.
  2. From the File menu, choose Open Project (ALT, F, O). Open VISDATA.MAK from the VB\SAMPLES\VISDATA directory.
  3. From the Run menu, choose start (ALT, R, S) or press F5 to run the program.
  4. From the Visual Data File menu, choose OpenDatabase. From the sub menu choose MS Access.
  5. From the Open MS Access Database dialog box, select the BIBLIO.MDB file.
  6. For the RecordSet Form Type, select Grid.
  7. Enter the following SQL statement in the SQL Statement window:
       Select SUM(PubID), SUM(Au_ID) From Titles						
  8. Click the Execute SQL command Button.
  9. The result shows in a grid window. The two fields have the same value. They should be different.
buglist3.00 3.00
Properties

Article ID: 101553 - Last Review: 10/22/2013 20:27:06 - Revision: 2.0

  • Microsoft Visual Basic 3.0 Professional Edition
  • Microsoft Visual Basic 3.0 Professional Edition
  • kbnosurvey kbarchive kbbug KB101553
Feedback