Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 205183 - Last Review: July 15, 2004 - Revision: 1.1
ACC2000: How to Create a Grouped Running Sum in a Query
This article was previously published under Q205183
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
This article shows you how to create a running sum per group in a Microsoft Access query.
The preferred method for producing running sums per group is to create an Access report. However, you can produce similar running sums in a query. The following example shows how you can create a running sum on a group of items in an Access query.
- Open the sample database, Northwind.mdb.
- Create the following new query based on the Products table.
Query: qryGrpRunSum
-------------------
Type: Select Query
Field: CategoryID
Table: Products
Sort: Ascending
Field: UnitsInStock
Table: Products
- In the Field row of the third column in the query design grid, type the following expression:
RunSum: fncRunSum([CategoryID], [UnitsInStock])
- Save the query as qryGrpRunSum, and then close the query.
- Create a new module, and then type or paste the following code:
Option Compare Database
Option Explicit
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long
If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If
'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
- Save the module as modGrpRunSum, and then close the module.
- In the Database window, double-click the qryGrpRunSum query to run the query. Note that the first column of the query displays the categories, the second column displays the units in stock, and the third column displays a running sum of the units in stock per category.
IMPORTANT: If you scroll up or down in Datasheet view of the query, the running sum values may change. This is expected behavior with this type of an Access query and with Microsoft Jet. If this occurs, scroll to the group that you want to view, and press F9 once or twice to accurately recalculate the sum for the group that is being displayed. You can also include criteria in the query to return only the group for which you want to see a running sum.
For more information about grouping records in an Access report, click
Microsoft Access Help on the
Help menu, type
group records in a report in the Office Assistant or
the Answer Wizard, and then click
Search to view the topics
returned.
For more information about creating running sums in an Access report, click
Microsoft Access Help on the
Help menu, type
calculate a running sum in a report in the Office Assistant or
the Answer Wizard, and then click
Search to view the topics
returned.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate