Results 1 to 8 of 8
  1. #1
    apk19 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    30

    Removing records when sum is zero and showing totals of records

    Hi all,

    Have a report that lists money from clients into an account from two separate tables (two different classes of clients).



    Created a report, however, I want to remove any client when the sum of their balance is zero. I also want to display only the sum of money instead of individual records. Currently it displays all records (in and out). The report has the grand total listed in the footer.

    It has been a while since I have inserted any coding into the database, and will need to know where and what.

    Much appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Rather bizarre requirement. Why would you want to remove data that supports the sum?
    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.

  3. #3
    apk19 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    30
    Hi June7,

    Don't necessarily want to remove the data that supports the sum, just want to display the sums for each record when they are not zero.

    For instance, the record in the screenshot below does not need to be displayed, but for any record where the total is not zero, I would like to be displayed in the report. The purpose is to show outstanding amounts.

    Click image for larger version. 

Name:	Example.JPG 
Views:	16 
Size:	27.6 KB 
ID:	31570

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Okay, remove did mean delete, means filter.

    Possible options:

    1. DSum() domain aggregate function
    SELECT * FROM tablename WHERE DSum("Amount","tablename","CustID=" & CustID)<>0

    2. Build an aggregate GROUP BY query that calculates the net sum and then join that query to the source table and apply filter
    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.

  5. #5
    apk19 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    30
    OK. Apologies for not revisiting this sooner.

    Managed to make progress using a Query Builder.

    Record Source for the report is:

    SELECT tblTrustAccount.ClientID, tblTrustAccount.SponsorID, Sum(tblTrustAccount.Amount) AS SumOfAmount FROM tblTrustAccount GROUP BY tblTrustAccount.ClientID, tblTrustAccount.SponsorID;

    Two problems:

    1. Entering Report View asks "Enter Parameter Value Amount".
    2. I do not know what to enter in the Filter field of the report to hide all values of only zero.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The parameter prompt likely means that field isn't spelled right. To exclude the ones that sum to 0:

    SELECT tblTrustAccount.ClientID, tblTrustAccount.SponsorID, Sum(tblTrustAccount.Amount) AS SumOfAmount FROM tblTrustAccount GROUP BY tblTrustAccount.ClientID, tblTrustAccount.SponsorID
    HAVING
    Sum(tblTrustAccount.Amount) <> 0
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    apk19 is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    May 2015
    Posts
    30
    Quote Originally Posted by pbaldy View Post
    The parameter prompt likely means that field isn't spelled right. To exclude the ones that sum to 0:

    SELECT tblTrustAccount.ClientID, tblTrustAccount.SponsorID, Sum(tblTrustAccount.Amount) AS SumOfAmount FROM tblTrustAccount GROUP BY tblTrustAccount.ClientID, tblTrustAccount.SponsorID
    HAVING
    Sum(tblTrustAccount.Amount) <> 0
    Perfect, Thanks!

    Added the extra line to the SQL.

    The Parameter value issue was =Sum[Amount] instead of =Sum[SumofAmount] which were the values preceding it.

    Thanks all.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 07-07-2016, 06:24 PM
  2. Replies: 1
    Last Post: 04-06-2016, 09:26 AM
  3. Removing Duplicate records
    By Brett1230 in forum Access
    Replies: 2
    Last Post: 01-28-2015, 01:22 PM
  4. Replies: 5
    Last Post: 10-27-2011, 10:25 AM
  5. Replies: 7
    Last Post: 12-07-2009, 07:27 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