locked
Running total in MS 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 : 

    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


    Sunday, April 12, 2020 11:52 PM

All replies

  • Hi Vincent,

    >>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.

    From your description, you want to run query in MS Access, your issue is related to MS Access, I suggest you open a thread in MS Access forums, People there will help you more effectively. 
    If you have some issues about SQL server query statement, you can open a new thread at the Transact-SQL forum to get better help.
    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, April 13, 2020 2:51 AM
  • Thank you for the reply,

    I didnt know such a thread existed, 

    Ill sure do.

    Vincent

    Monday, April 13, 2020 12:38 PM
  • Thank you for the reply,

    I didnt know such a thread existed, 

    Ill sure do.

    Vincent

    Hi Vincent,

    Thank you for your update. If the reply could help you, please mark the useful reply as answer.This can be beneficial to other community members reading the thread.
    In addition, if you have another questions, please feel free to ask.
    Thanks for your contribution.

    Best regards,
    Cathy  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, April 14, 2020 2:39 AM