Running totals in Access RRS feed

  • Question

  • Hi

    I managed to optain the desired outcome in SQL server via the following query : 


    "SELECT [Date],[Point de livraison],RIGHT([NomProduit],LEN([NomProduit])-1) as Produit,[Inventaire],sum([Inventaire]) OVER (PARTITION BY [Point de livraison],[NomProduit] ORDER BY Date) As CummulInventaire,ID FROM [dbo].[QueryReceptionExpedition] WHERE Date between '" & Date_de_départ & "' and '" & Date_de_fin & "' AND [Point de livraison] ='" & Batiment & "' AND RIGHT([NomProduit],LEN([NomProduit])-1)='" & Produit & "' ORDER BY Date"


    Here's the problem, it doesnt work directly in Access. as i managed to handled the nulls in SQL server, but idk how in access.

    the thing is that i have two table from wich i want to create my query. 

    The 1st table contains all the dates from 2017 to 2019

    The 2nd table contains a the inventory movement of a product, that occur only a few time between 2017 to 2019

    What i want to do is to is to create a query that contain all the dates from the 1st table and the running total of the inventory movement from the second table.

    For exemple : 

    1st Table : 





    2nd table :

    04/04/2018     750

    desired query :

    01/01/2017       0

    02/01/2017       0


    04/04/2018     750


    31/12/2019     750

    I hope im clear enough.

     That you for your time :)


    Monday, April 13, 2020 12:37 PM

All replies

  • You can save your SQL query as a View, then attach that view to Access just like you do with linked tables.

    Access reports have good support for Running Sums - see the property at the control level.

    -Tom. Microsoft Access MVP

    Monday, April 13, 2020 2:20 PM
  • If you absolutely must return these results in an Access query you can get your running sum using the DSum() function (see https://docs.microsoft.com/en-us/office/troubleshoot/access/create-a-running-totals-query) or use a correlated subquery (see http://allenbrowne.com/subquery-01.html)


    Monday, April 13, 2020 4:06 PM
  • The most efficient means of returning balances  (running sums) is by joining two instances of the relevant table.  It can also be done, albeit less efficiently, by means of a correlated subquery.  The VBA DSum function should only be used where it is necessary to return an updatable recordset, i.e. as a form's RecordSource.

    You'll find examples of each of the above solutions in Balances.zip in my public databases folder at:


    The demo uses tables of financial transactions for its examples, though the same methodology applies in other contexts.  For an example of the basics of inventory management see Inventory.zip in the same OneDrive folder.

    Ken Sheridan, Stafford, England

    Monday, April 13, 2020 4:56 PM