Results 1 to 9 of 9
  1. #1
    wendtb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    5

    Database Buildout: Running Sum by Both GROUP and Transaction Date


    Hi everyone,

    I need your help and I am sure someone has answered this before, but I cannot seem to find it. I am trying to create a query to create a rolling sum of the balances of all "Type_of_Transaction" except for "Total Commitment" By Loan_ID and by Transaction_Date. I provided a snapshot below of the simple table but here is a written example as well. I really hope someone can help, I have been struggling for weeks and I am not as good as I thought.

    Basically I want to say 6600119 funded 9.75 M on September 26th and then on 4/20 paid down 154k for a balance of 9.596M and then funded another 100k for a balance as of 4/28 of 9.696M. Is there a way to calculate a running balance like that for each individual loan number? You would be a lifesaver.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	22 
Size:	10.7 KB 
ID:	33660

    Thank you in advance!
    Brandon

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Create a new aggregate query by clicking Totals in the design ribbon.
    Add loan id and type of transaction fields and note they will say group by in the totals row.
    Now add the amount field and change group by to sum using the dropdown.
    That's it. Run the query
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    wendtb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    5
    Hey Colin,

    This is not allowing me to do a running sum, I also want to include the running sum by date and exclude a certain type of transaction. Here is the SQL I have so far:

    SELECT Transactions.Loan_ID, Transactions.Type_of_Transaction, Sum(Transactions.Amount) AS SumOfAmount
    FROM Transactions
    WHERE (("Transaction Date">="Transaction Date"))
    GROUP BY Transactions.Loan_ID, Transactions.Type_of_Transaction
    HAVING (((Transactions.Type_of_Transaction)<>"Total Commitment"));

    The two things I cannot figure out are how to do the cumulative sum and by date. I included a snip from excel of how I want it to look in access.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	17 
Size:	14.9 KB 
ID:	33661


    THANK YOU SO MUCH FOR THE HELP!

  4. #4
    wendtb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    5
    Essentially I am trying to perform this formula in MS Access.

    Sum amounts if loan ids are equal, type of transaction does not equal Total Commitment, and I want to sum all transactions up to the current date of the transaction in that row (Cumulative sum).

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah I see. Bit more complicated than it originally seemed to be
    Have you looked on allen browne's website? He may well have a solution.

    Failing that, suggest you upload some sample data for someone to play with
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm assuming your example has two Loan_ID's which coincidentally have the same transaction values

    I suggest the query you want is

    SELECT A.Loan_ID, A.Transaction_Date, A.Type_of_Transaction, sum(iif(B.Type_of_Transaction="TotalCommitment",0, B.Amount)) as Outstanding Balance
    FROM Transactions A INNER JOIN Transactions B ON A.Loan_ID =B.Loan_ID AND B.Transaction_Date<=A.Transaction Date
    GROUP BY A.Loan_ID, A.Type_of_Transaction, A.Type_of_Transaction
    ORDER BY A.Loan_ID, A.Transaction_Date

    Note this cannot be created in the query builder because it cannot create the join B.Transaction_Date<=A.Transaction Date

  7. #7
    wendtb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    5
    THANK YOU, WOW. I am so happy, cannot thank you enough. Is there anyway you can explain to me what the formula is doing? It seems like it is just joining the same table and creating an ALIAS for it?

    THANK YOU AGAIN AND AGAIN!

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    there is no formula as such, just done with joins

    you need to use an alias because you are referencing the same table twice and it joins on itself.

    The first alias (A) provides the 'reference' data, the second (B) provides the summed data 'filtered' by the join.

  9. #9
    wendtb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    5
    Got it, thank you again!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 02-10-2016, 10:00 AM
  2. MS Query Last Transaction Date Formula Required
    By Maroota in forum SQL Server
    Replies: 1
    Last Post: 02-27-2015, 08:20 AM
  3. Replies: 2
    Last Post: 07-17-2014, 01:46 AM
  4. Replies: 8
    Last Post: 06-05-2013, 03:39 PM
  5. Replies: 7
    Last Post: 11-14-2011, 05:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums