Trouble Making a Running Sum in Access Query

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 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 Sum by Date criteria in a query (Duplicate Dates)

I think the easiest thing will be to just refer to running sums for all the controls you want to add. For example "31 to 60 Days" is text29 on your report. Create a hidden control called, say, R31to60 and set to Running sum over group, then in the footer, put a text box and set the control source to:

=[R31to60]

It will show the last value for the running sum, that is, the total.

In design view, the highlight shows the running sum control and total. The control can be shrunk down and hidden.

In report view you can see the "total" field shows the last value for running sum.

Sum of orders Based on Customer Name

It is unclear what running sum you mean but assuming a running sum of quantity as order number increases for each customer. We can use either Dlookup or a correlated subquery for the running sum.

First Foreign Keys are not Primary Keys. You do not have to remove the space in the variable names. I just couldn't get the sql with all the inner joins to work and thought the space might be the problem.

Working from qryCustomerOrderLineItems and using Dlookup for the running sum:

Total: Min(DSum("Quantity","qryCustomerOrderLineItems","OrderNumber<= " & [OrderNumber] & " AND CustomerID= " & [CustomerID]))
'to group by price or size just expand the where clause of the DSUM
'min, max, first, and last are equivalent for grouping 1 number
' in the totals row access changes summary functions like min to expression by moving them inside

You can also grab everything from qryCustomerLineItems and or use a correlated subquery for the running sum

Total: (SELECT Sum(Quantity) FROM [qryCustomerOrderLineItems] AS q WHERE q.OrderNumber <= [qryCustomerOrderLineItems].[OrderNumber]
AND q.CustomerID=[qryCustomerOrderLineItems].[CustomerID])

Both queries give the same result:

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.

Add a summary row to MS Access query

You can use a union query:

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, Sum(a) As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

EDIT:

SELECT "" As Sort, a,b,c FROM Table
UNION ALL
SELECT "Total" As Sort, "" As A, Sum(b) As b, Sum(c) As C FROM Table
ORDER BY Sort

---

Related Topics