Article ID: 101553 - View products that this article applies to.
This article was previously published under Q101553
This article has been archived. It is offered "as is" and will no longer be updated.
When an SQL query statement contains multiple aggregate functions, the result set incorrectly contains the same value for all the functions. The result of the first aggregate function is duplicated in the result column of all of the other functions.
Aggregate functions typically do not contain explicit column names for expressions in the SQL query. In SQL queries containing multiple aggregate function calls, the Access database layer does not uniquely identify the return columns for any functions past the first. Therefore, it duplicates the result column of the first function in the result columns of the succeeding functions
This problem did not occur in Visual Basic version 2.0. In Visual Basic version 3.0, the Microsoft Access engine was integrated into the data access functionality. The Microsoft Access engine tracks the column by name, whereas Visual Basic version 2.0 tracks the column by the column offset.
Use aliases for the aggregate functions to solve the problem. Replace the SQL statement shown below in the "Steps to Reproduce Problem" section with the following SQL statement, which contains the aliases One and Two for the column names for the separate SUM expressions:
The Alias names can be anything other than the column name and must be unique within the statement.
After inserting the aliases, run the SQL statement again and notice that the two fields now correctly show the different results.
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.
Steps to Reproduce Problem
Article ID: 101553 - Last Review: October 22, 2013 - Revision: 2.0