Running Sum in Access Query with Group By

Running sum in Access query with Group By

You have several options. One, however, won't do, and that is a recursive query using SQL only; Access can't be fooled and will claim about a circular reference. Your only chance is to create a query resolving a limited number of levels only, say, 8 or 10.

But you can cover the recursive call in a domain aggregate function like DLookup. This is, however, very slow as DLookup calling the query will run for each and every record. For more than some dozens of records this will most likely be unacceptable.

The fastest way, for an unlimited number of levels, I've found, is to create a lookup function which walks the tree for each record. This can output either the level of the record or a compound key build by the key of the record and all keys above.

As the lookup function will use the same recordset for every call, you can make it static, and (for JET/ACE) you can improve further by using Seek to locate the records.

Here's an example which will give you an idea:

Function RecursiveLookup(ByVal lngID As Long) As String

  Static dbs      As Database
  Static tbl      As TableDef
  Static rst      As Recordset

  Dim lngLevel    As Long
  Dim strAccount  As String

  If dbs Is Nothing Then
    ' For testing only.
    ' Replace with OpenDatabase of backend database file.
    Set dbs = CurrentDb()
    Set tbl = dbs.TableDefs("tblAccount")
    Set rst = dbs.OpenRecordset(tbl.Name, dbOpenTable)
  End If

  With rst
    .Index = "PrimaryKey"
    While lngID > 0
      .Seek "=", lngID
      If Not .NoMatch Then
        lngLevel = lngLevel + 1
        lngID = !MasterAccountFK.Value
        If lngID > 0 Then
          strAccount = str(!AccountID) & strAccount
        End If
      Else
        lngID = 0
      End If
    Wend
    ' Leave recordset open.
    ' .Close
  End With

'  Don't terminate static objects.
'  Set rst = Nothing
'  Set tbl = Nothing
'  Set dbs = Nothing

'  Alternative expression for returning the level.
'  (Adjust vartype of return value of function.) '  RecursiveLookup = lngLevel ' As Long
  RecursiveLookup = strAccount

End Function

This assumes a table with a primary key ID and a foreign (master) key pointing to the parent record - and a top level record (not used) with a visible key (AccountID) of 0.

Now your tree will be nicely shown almost instantaneously using a query like this, where Account will be the visible compound key:

  SELECT
    *, RecursiveLookup([ID]) AS Account
  FROM
    tblAccount
  WHERE
    AccountID > 0
  ORDER BY
    RecursiveLookup([ID]);

Running total in Access Query based on item and date

If you're going to use DSum, you need to format your string. But you should rarely ever use a domain aggregate in a query. In nearly all cases, you should just use a subquery instead.

Also, you need to specify the part ID when summing as well, so include it in the WHERE clause of the subquery (or domain aggregate).

PartTotal: (SELECT SUM([PartTransactions]) FROM DmPartsT t WHERE t.[RecordDate]<=  DMPartsT.[RecordDate] AND t.PartID = DmPartsT.PartID)

This avoids errors with casting back and forth to a string, and allows the optimizer to analyze and optimize your query.

Running Total in Ms-access query using SQL

You have repeated "From" in your correlated subquery:

  ( Select Sum(x2.[Avg Revenue]) 
    From FROM [BrandRevenue] x2
    ...

I don't think you need the subquery in the from clause, or the GROUP BY either. I think this will be better, and certainly simpler:

SELECT  x.SubCategoryID,
        x.brand,
        x.[Avg Revenue],
        (   SELECT  SUM(x2.[Avg Revenue]) 
            FROM    [BrandRevenue] x2
            WHERE   x2.[SubCategoryID] = x.[SubCategoryID] 
            AND     x2.[Avg Revenue] <= x.[Avg Revenue]
        ) AS [Running Sum]
FROM    BrandRevenue AS x
ORDER BY x.SubCategoryID, x.[Avg Revenue] DESC;

ADDENUM

I think to get around the problem of Brands having the same revenue you will need to add some additional logic to your correlated subquery:

SELECT  x.SubCategoryID,
        x.brand,
        x.[Avg Revenue],
        (   SELECT  SUM(x2.[Avg Revenue]) 
            FROM    [BrandRevenue] x2
            WHERE   x2.[SubCategoryID] = x.[SubCategoryID] 
            AND  (  x2.[Avg Revenue] <= x.[Avg Revenue]
                OR  (x2.[Avg Revenue] = x.[Avg Revenue] 
                AND x2.Brand <= x.Brand
                )
        ) AS [Running Sum]
FROM    BrandRevenue AS x
ORDER BY x.SubCategoryID, x.[Avg Revenue] DESC, x.Brand;

How Do I Find the Minimum Value of a Running Total by Group In SQL?

Syntactically, your first query cannot have been compilable in MS Access as the RunningTotal correlated aggregate expressoin should be included in GROUP BY clause. Practically though, correlated subqueries may not be allowed in GROUP BY and you also do not want to group by this rank calculation during aggregation. Also, in MS Access running complex operations on an underlying UNION query does yield performance issues.

Consider the following setup:

Convert underlying union query to temp table:
```
SELECT * INTO mytemptable FROM myUnionQuery
```

Calculate running total (quasi aggregation).

SELECT t.[ItemCode], t.[WarehouseCode], t.[Date], 
       (SELECT SUM(sub.[Qty]) FROM myTempTable sub
        WHERE sub.[ItemCode] = t.[ItemCode] 
          AND sub.[WarehouseCode] = t.[WarehouseCode]
          AND sub.[Date] <= t.[Date]) AS RunningQtyTotal
FROM myTempTable t;

Aggregate at [ItemCode] and [WarehouseCode] levels:

SELECT q.[ItemCode], q.[WarehouseCode], MIN(q.[RunningQtyTotal]) AS MinRunQtyTotal
FROM mySelectQuery q
GROUP BY q.[ItemCode], q.[WarehouseCode]

Trouble making a running sum in Access query

If you create a report, there is a property to calculate a running sum.

If you prefer a query, you can use a subquery to calculate the running sum:

SELECT  DatePart("yyyy",[EoL]) AS AYear
,       Count(eq1.Equipment) AS EquipCount
,       (
        SELECT  Count(eq2.Equipment)
        FROM    EquipmentQuery eq2
        WHERE   DatePart("yyyy",eq2.[EoL]) <= DatePart("yyyy",eq1.[EoL])
        ) AS RunningSuma
FROM    EquipmentQuery AS eq1
GROUP BY 
        DatePart("yyyy",[EoL])
ORDER BY 
        DatePart("yyyy",[EoL]);

Running total within a category in Access

I can't tell about its efficiency but it works:

select t.*,
(select sum(Multiple) from tablename where Item = t.Item and [No] <= t.[No]) +
(select InventoryPos from tablename where Item = t.Item and [No] = (
  select min([No]) from tablename where Item = t.Item)
)
from tablename as t

Running total with subgroups

You're close, but your current query is doing the SUM over all rows in each group. You just need to add a row specification for your cumulative sum:

Sum(sales-returns) over(
  partition by Version, Country 
  Order by Week 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW -- all rows including current row
) Running_stock

TD Manual

Running Sum in Access Query with Group By