However, this doesn’t exist in a query and to mimic the report feature to recreate running totals query in Microsoft Access, you will need to be a little creative!

Even though MS Access queries you do have a ‘group by’ and ‘where clause’ features where you can choose one of the aggregate functions, there is no running sum element and the trick will be to utilise an Access function (or two).

By identifying a key field to act as the lever or pivoting data, you can tell Access to keep count and total another field. 

How To Create A Running Totals Query In Microsoft Access – The Steps

Here’s an example of what I mean. 

I want to create an Access query to show a list of employees using their unique identifier and report on both their individual total sales order amount and progressively add the previous rows (or records) in the same list (as shown below).

Create a blank query and in design view add your tables or queries. I’m using two tables which must be joined in a relationship (otherwise you will have unexpected results). The two tables are:

Add the fields you require and make sure you include the pivoting field, the output fields and at least one field to group by. In my example, I have [Employee ID] as an alias ‘Employee ID Group’, Employee Name which is an expression with ‘[Last Name] & “, ” & [First Name]’ and the [Order Amount].

Switch to the Groups/Total function for your query so you can see the ‘Total‘ row appear in the grid view:

Set the aggregate function required. In my example, I’ve use the Sum function across the [Order Amount] field.

Now the missing element to mimic the running totals query in Microsoft Access and that is to create a new field with an expression like:

Running Total: CDbl(DSum("[Order Amount]","Orders","[Employee ID]<=" & [Employee ID Group] & ""))

I have used two Access functions; CDbl and DSum. The first function will convert all data into a real number should you wish to further calculate with it later on and is purely optional. The main function is the DSum which works as follows:

The [Order Amount] is the field to total (sum) from the source table called ‘Orders‘ so the first two parameters are very clear and easy to follow. The third part to this function uses the unique identifier ([Employee ID]) from the source table and logical tests for the output [Employee ID Group] field (less than or equal to) to the same unique identifier so if the value is the same or less than the other records, add the totals order amount value to it.

This will only work for a numeric comparison as a text data type will make it more complex (though do-able). The other important element is to ensure you sort in the order you want to keep count so with the [Employee ID Group] field, I have set this sort in ascending order.

Save your query and run.

By locating the key field to logically test value and output fields to calculate, you can create various different types of queries and it doesn’t have to be using just DSum.

Why not check out my Microsoft Access database eBooks and get a good grounding in how to really release the power of MS Access. They all come with a money back guarantee and 30 day email support.

Tags: access query properties, dsum function in ms access, how to create a query, MS Access, ms access queries, totals query in microsoft access