Results 1 to 5 of 5
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Qry to show total loan amount and remain balance

    Not sure if this is possible in a single qry (now realizing this might be multiple qrys feeding into a table? but I am not able to see something that big yet)



    I have a table with all of my transactions, including when I have issued Loans to chapters and when they have made payments.

    I have many chapters, and some chapters have taken out loans, and some of them have made payments.

    I am trying to get a qry (or actually a subform) that will show


    CHAPTER TOTAL LOAN AMNT REMAIN BAL
    chapter01 $1000 $700
    chapter07 $2500 $2500






    not sure if this is helpful information but all loan transactions are entered under TRANSACCOUNT "Loans"
    and all Chapters have their own TRASCODE
    Click image for larger version. 

Name:	Capture.PNG 
Views:	27 
Size:	98.5 KB 
ID:	43311

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Its been a while since i have added attachments
    Hopefully this works

    Username: Please
    Password: Help

    OHC DB.zip

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    This will probably do
    Code:
    SELECT trans1.Payee, -1*trans1.Amount AS TotalLoanAmount, Nz(trans2.Amount,0) AS TotalPaymentAmount, -1*(trans1.Amount + Nz(trans2.Amount,0)) AS RemainingLoanBalance
    FROM
    (SELECT t1.Payee, SUM(t1.TransAmount) AS Amount FROM tbl_Transactions t1 WHERE t1.TransAccountName = 4 AND t1.TransType = 1 GROUP BY t1.Payee) AS trans1
    LEFT JOIN
    (SELECT t2.Payee, SUM(t2.TransAmount) AS Amount FROM tbl_Transactions t2 WHERE t2.TransAccountName = 4 AND t2.TransType = 2 GROUP BY t2.Payee) AS trans2 ON trans2.Payee = trans1.Payee;

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Note: In table tblTransactions, field name "Memo" is a reserved word and shouldn't be used for object names. "TransMemo" would be a better name.....

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

Similar Threads

  1. Query to extract balance payment amount
    By Alex Motilal in forum Queries
    Replies: 1
    Last Post: 01-11-2020, 04:11 PM
  2. Replies: 7
    Last Post: 06-15-2017, 02:36 AM
  3. Running Total on Amount for each Month?
    By aellistechsupport in forum Queries
    Replies: 4
    Last Post: 04-24-2014, 01:18 PM
  4. Replies: 1
    Last Post: 03-29-2014, 10:19 AM
  5. total amount
    By sdc1234 in forum Queries
    Replies: 12
    Last Post: 02-19-2014, 03:37 PM

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