Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22

    Function to aggregate negative and positive values over time

    Hi all,

    I am trying to have a column in my query that adds aggregates amounts of payments and advances over time.
    In my table i have a payment column of only negative numbers and an advance column of positive numbers. I would like my query
    to add another column as the outstanding balance after each record.

    For example, I want the table to show this:
    DATE(ascending)| PAYMENT | ADVANCE | NET | BALANCE


    2018/01/01 | (100,000.00$) | 0.00$ | (100,000.00) | (100,000.00$)
    2018/01/02 | 0.00$ |500,000.00$ | 500,000.00 | 400,000.00$
    2018/01/03 | (500,000.00$) | 200,000.00$ | (-300,000.00) | 100,000.00$

    So far, Ive added a column in a query with the following build function:
    Balance: DSUM("[NET]", "REVOLVING CAD","[DATE]>="&[DATE])

    The problem is that the balance column shows 0 in all its fields, and if I change the operand to <= , the fields are blank.

    what to do???

    Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    can you make a query and turn on summation,
    then sum([adv]-[paymt])

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    This appears to be 'running sum/balance'. Easily done in a report.

    Use # delimiters:

    Balance: DSUM("[NET]", "REVOLVING CAD", "[DATE]>=#" & [DATE] & "#")

    Although you might want <= instead

    Review: http://allenbrowne.com/subquery-01.html#YTD

    Domain aggregate functions can perform very slowly in large datasets. Sometimes even subqueries are slow.

    Date is a reserved word (intrinsic function). Should not use reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    This worked perfectly and I changed it to <= except it does not take into consideration records entered with the same date
    For example: I might enter a record for January 1st and subsequently add another record for that same date. Using this formula, the balance will be the same.
    Im not sure to understand your comment at the end "Domain aggregate functions can perform very slowly in large datasets. Sometimes even subqueries are slow.
    Date is a reserved word (intrinsic function). Should not use reserved words as names for anything."

    Thank you !!

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, the balance will be the same. Need some more criteria in the expression? Like an account number? Maybe build a report and use its Sorting & Grouping features and calcs in textbox. Textbox on report has a RunningSum property. However, probably see same issue if there is no additional criteria for sorting and grouping data.

    My last comment was "Should not use reserved words as names for anything." What is not clear? Date is a reserved word because it is an intrinsic function. You have used Date as a field name. This is not advisable.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Thank you so much for the prompt replies. I apologize as I am still learning access at the moment. I understood why Date can't be used now.

    As for the balance, I need it to have it updated even if the date is the same because the records are different and therefore the balance should reflect the change.

    I made this query to have a report linked to it to show the outstanding balance for that particular account for a specific period in time. How would I be able to modify the function build to have the balance updated ??

    Thank you

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe include time in the date value? Or perhaps also include unique ID (such as autonumber) field in the aggregate criteria?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Im not sure how to include the unique ID in the aggregate criteria ? Ive posted an example of how it looks.

    Transaction Date Paiement Avance ID Net Balance
    2018-04-18 (200 000.00 $) 100 000.00 $ 20 (100 000.00 $) -100000
    2018-04-25 0.00 $ 200 000.00 $ 14 200 000.00 $ 100000
    2018-05-01 0.00 $ 300 000.00 $ 24 300 000.00 $ 400000
    2018-05-08 (90 000.00 $) 0.00 $ 21 (90 000.00 $) -1265000
    2018-05-08 (100 000.00 $) 500 000.00 $ 22 400 000.00 $ -1265000
    2018-05-08 (2 000 000.00 $) 25 000.00 $ 23 (1 975 000.00 $) -1265000
    2018-05-16 (200 000.00 $) 100 000.00 $ 18 (100 000.00 $) -1365000

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If ID is an autonumber, normally it would be increasing along with the date sequence. So why is ID for 2018-04-18 greater than ID for 2018-05-16?

    But for the above, I would have suggested:

    Balance: DSUM("[NET]", "REVOLVING CAD", "[DATE]<=#" & [DATE] & "# AND ID<" & [ID])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Because it is possible to add a record in for with a value date in the past. Ive modified the function build but still an issue:
    Transaction Date Paiement Avance ID Net Balance
    2018-04-18 (200 000.00 $) 100 000.00 $ 20 (100 000.00 $)
    2018-04-25 0.00 $ 200 000.00 $ 14 200 000.00 $
    2018-05-01 0.00 $ 300 000.00 $ 24 300 000.00 $ 100000
    2018-05-08 (90 000.00 $) 0.00 $ 21 (90 000.00 $) 100000
    2018-05-08 (100 000.00 $) 500 000.00 $ 22 400 000.00 $ 10000
    2018-05-08 (2 000 000.00 $) 25 000.00 $ 23 (1 975 000.00 $) 410000
    2018-05-16 (200 000.00 $) 100 000.00 $ 18 (100 000.00 $) 200000

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you need to create a calculated ID based on concatenating the columns that matter - in your example date and id e.g.

    tmpID: clng(clng(date) & id)

    then you can calculate your running sum based on that.

    so your first query would be something like

    Code:
    select *, clng(clng(date) & id) as tmpID FROM myTable
    - note the &, you don't add

    and then a second one to calculate the values. Something like

    Code:
    Select qry1.*, sum(qry2.amount) as balance
    FROM qry1 AS Qry2 INNER JOIN qry1 On qry2.tmpID<=qry1.tmpID
    Note this sql uses an alias to call the same qry1 twice and by using the <= in the join, can only be written in sql, the query builder won't handle to join. But a cheat is to use the query builder, join on tmpID, then go to the sql window and change the = to <=, but make sure you have it the right way round

    alternative is to use a cartesian query which can be done in the query builder

    Code:
    Select qry1.*, sum(qry2.amount) as balance
    FROM qry1 AS Qry2, qry1 
    WHERE qry2.tmpID<=qry1.tmpID
    or you can use sub queries or domain queries but they will be slower still.

  12. #12
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22
    Hi Ajax, I'm extremely lost now. My initial intention was to create a database for different accounts linked to a company.

    In each account, there are payments (negative amounts) and advances (positive amounts). After each record, there should be an outstanding balance which reflects the position of that account at given point in time. Records with a value date in the past can be inserted as well. So I am not sure if im going in the right direction.

    Ideally I would like to simply have a table with that has the following headers: Date of transaction - Payment - Advance - ID - Net (advance-payment) - Outstanding Balance

    So what I did was to create a table with all those headlines mentioned without the outstanding balance as I didint think it was possible and then created a query to have the outstanding balance calculated...

    I am in the right direction or is there a simpler way of approaching this problem?

    Thank you again

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    It would seem you are using an excel mentality to get to what you want.

    I’ve done loads of account systems over the years . In my experience a single column with pos and neg values is the way to go, then use the queries I provided to get your balance. Easy enough to split the amounts to separate columns in the query

  14. #14
    ismafoot is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    May 2018
    Posts
    22

    Red face

    Quote Originally Posted by Ajax View Post
    It would seem you are using an excel mentality to get to what you want.

    I’ve done loads of account systems over the years . In my experience a single column with pos and neg values is the way to go, then use the queries I provided to get your balance. Easy enough to split the amounts to separate columns in the query
    I would, but for aesthetic purposes its more convenient to have a column for advances and another for payments. Especially because most people who will use this interface have little to no knowledge at all on the microsoft suite.

    You are right, I a have an excel mentality as I have worked on it since the dawn of time. However, I thought of jumping into access for this project. I realized the benefits but also the complexity behind it.

    Is there anyway i can send you my progress and you tell me what I am doing wrong?

    Thank you

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You can but I’m pretty busy at the moment so may take a while to reply. One of the significant differences between Access and excel is excel combines data storage and presentation in one view whilst access uses tables for data storage/relationships and queries/forms/reports for presentation. Tables and relationships should be designed for optimum performance, not presentation

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  2. Comparing positive to negative values.
    By rubberducky in forum Access
    Replies: 1
    Last Post: 01-20-2014, 04:44 PM
  3. Replies: 2
    Last Post: 06-05-2013, 09:37 AM
  4. Replies: 7
    Last Post: 09-16-2011, 01:39 PM
  5. Convert Negative Values to Positive
    By creativefusion in forum Queries
    Replies: 1
    Last Post: 10-21-2009, 02:47 AM

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