Running totals in Access

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 : 

01/01/2017

02/01/2017

...

31/12/2019

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 :)

Vincent

Answer 1

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

Answer 2

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)

-Bruce

Answer 3

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:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

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