Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Oct 2009
    Posts
    13
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Using DSum in a query

    I have a database that tracks Client's receipts and expenditures for several cataqories such as Clothing, Medical, Personal, Savings, and Special in a table called Activities. I need to create a report that gives me monthly balances for each catagory as well as a total balance each month. I was trying to use DSum to give me a running sum for each catagory but I can't get it to work properly. The statement I have tried to use for the Clothing catagory in a query is DSum("[Clothing]","Activities","[ClientID]"). It gives me the same value for each month. I would appreciate any help.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jim,

    How about using a sum query then write a report to get the over all totals. See attached for query. I just made up a table design that seemed to be what you were using as I understood it.
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Why don't you zip a compacted database with sensitive data removed and we can then see what your table looks like.
    What is that DSUM above meant to do?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by jimbadge View Post
    DSum("[Clothing]","Activities","[ClientID]").
    The third part of Dsum specifies criteria. So it might be
    =DSum("[Clothing]","Activities","[ClientID]=" & [clientID])
    The second clientID is the value of clientID for the current record, so you are asking it to add Clothing, from table (or query)Activities where the clientID is equal to the current clientID.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •