Results 1 to 4 of 4
  1. #1
    justdone is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2020
    Posts
    5

    Previous Balance Copied From Calculated Field in a Query

    I’m making a query for an installment payment history which I named PaymentHistoryQ with the following fields:

    OrderID from OrderTbl


    TotalDue from OrderTbl
    PaymentID from PaymentTbl
    DatePaid from PaymentTbl
    AmtPaid from PaymentTbl
    TotalPaid which is a DSUM of AmtPaid per OrderID
    Balance: [TotalDue] - [TotalPaid]

    As I am creating a payment history table, I intend to make a ‘PrevBalance’ field which will serve as a beginning balance. The first record must be equal to the amount in ‘TotalDue’ while the succeeding records must copy the amount from ‘Balance’.

    I have tried using this query but there seems to be a problem.
    PrevBalance: (SELECT TOP 1 Dupe.Balance FROM PaymentHistoryQ AS Dupe WHERE Dupe.OrderID = OrderTbl.OrderID AND Dupe.DatePaid < PaymentTbl.DatePaid ORDER BY Dupe.DatePaid DESC)

    I would really appreciate your help.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It reads like you're trying to make a database table act like a spreadsheet - not a good idea. Or maybe your effort will end up in a form - I can't tell.
    There are lots of samples and videos on how to do a running sum or balance in a query. Here's just one.
    Basically you do a DSum over the payment field (in your case) to get the sum of payments up to the current record. You'd then subtract that value from the beginning value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    justdone is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Sep 2020
    Posts
    5
    It’s not a spreadsheet, I’m creating it for a subform.

    I have done the DSUM already and it’s working perfectly, no problem with that. My issue is copying the amounts in ‘Balance’ and using it like a beginning balance. Something like

    TotalDue = 2000

    Balance PrevBalance
    1000 2000
    2500 1000
    3000 2500
    1800 3000

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you have an ID (e.g. autonumber field) then I believe you'll need a subquery that gets the Max of ID where that value is less than the current ID and that recordset should be ordered by ID ascending. If you remain stuck you could post a zipped copy of your db and you might get more responses.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 3
    Last Post: 08-30-2020, 10:56 AM
  2. Replies: 6
    Last Post: 06-30-2019, 06:15 AM
  3. Replies: 9
    Last Post: 07-03-2015, 04:12 AM
  4. Replies: 14
    Last Post: 03-25-2015, 12:49 PM
  5. Replies: 1
    Last Post: 03-08-2015, 10:35 AM

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