Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    How to use SUM function in calculations

    I have a table that contains dated transactions that include Debits and Credits. There are also fields that designate particular types of transactions, e.g., "Type" (Integer).



    I'd like to have a query that returns the difference between the total of Debits and Credits for a given "Type" of transaction. Like Bal AS (SUM(Credits) - SUM(Debits))

    What would be the SELECT statement be for something like this?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    more like

    sum(Credits-Debits) AS Bal

    if debits and credits can be null then


    sum(nz(Credits,0)-nz(Debits,0)) AS Bal

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Hummm! Don't know what to do with condition depicted in the screenshot below when I attempted to run the query from a design view. (And yes, in any given record one or the other will be Null)
    Click image for larger version. 

Name:	003.jpg 
Views:	29 
Size:	47.5 KB 
ID:	36865

  4. #4
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Is this what you want?

    Cheers,
    Vlad
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    In your query, you have fields Debit, Credit, Debit's and Credits! Access doesn't like at least one of them (Credit), and says there is not such field.

    Having both components in same single row, there is no need for SUM(). Probably the query must be
    Code:
    SELECT Debits, Credits, Nz(Debits,0)-Nz(Credits,0) AS Balance FROM TblRegister

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Yes, PERFECT. How do I access Bal? I can envision an invisible form with a control bound to Bal but how could I do that in a general module? DAO Recordset?

    Also, there's a transaction date field so I'd like to add something like TDate <= #06/01/2018# so I can obtain Bal as of a certain date. Where does that go in the current scheme? Not in the Criteria I found.

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    RATS! I thought I caught that earlier........... sorry

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    save it as a query and use a dlookup in your module.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Of course! Now you know why the title of "Old Geezer".......... with age comes one forgets everything they know.

    BTW, will it suffice to add "TDate <= #" & MyDate & "#" to obtain "Bal" as of MyDate?

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I'm afraid not, you should build that in a query and do your dlookup on that. Have you had a chance to look at the file I've uploded? In the query you can add criteria to the TransactionDate field.
    Cheers,
    Vlad

  11. #11
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I tried that but got multiple records rather than a single record with the calculated "Bal". What I need is the balance as calculated based on all transactions made prior or on the date specified. I can build the query SELECT string in code, save it and do the DLookup on that. (I.e., what you see below gets me multiple records.)

    Click image for larger version. 

Name:	004.jpg 
Views:	23 
Size:	50.4 KB 
ID:	36875

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You get multiple records because you group by the TDate (and so you get the totals grouped by the transaction dates); change the GroupBy on the Total row to Where and you should get what you need.

    Cheers,
    Vlad

  13. #13
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Okay, thanks.
    Bill

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    and you Bal needs to be summed, not expression

  15. #15
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not really Ajax, he already summed the two fields TDebit and TCredit.

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

Similar Threads

  1. Calculations
    By Burnsie in forum Forms
    Replies: 7
    Last Post: 02-22-2018, 06:15 PM
  2. Calculations
    By carlislewwtp in forum Access
    Replies: 3
    Last Post: 01-29-2018, 02:32 PM
  3. Replies: 2
    Last Post: 02-26-2017, 11:31 AM
  4. Replies: 30
    Last Post: 09-30-2015, 10:58 AM
  5. Replies: 19
    Last Post: 09-05-2014, 06:13 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