Results 1 to 8 of 8

Using dsum to return a cumulative total

  1. #1
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17

    Using dsum to return a cumulative total

    I have been trying to follow threads that would allow me to return a cumulative total. I actually would like a cumulative percent. I can't seem to get the criteria right.

    My query name that I am pulling from is:
    "BeyondDeliveryDays"

    It has 3 fields:
    Agent
    DeliveryDaysBeyondCommitment
    CountofAgent

    For Every "agent", I want to have the cumulative total or percent of the "DeliveryDaysBeyondCommitment".

    Here is an example:
    Agent DeliveryDaysBeyondCommitment CountofAgents CumulativeTotal CumulativePercent
    ABC 0 100 100 79.37%
    ABC 1 5 105 83.33%
    ABC 2 10 115 91.27%
    ABC 3 3 118 93.65%
    ABC 4 8 126 100.00%
    DEF 0 25 25 47.17%
    DEF 4 3 28 52.83%
    DEF 5 5 33 62.26%
    DEF 8 7 40 75.47%
    DEF 10 8 48 90.57%
    DEF 11 5 53 100.00%


    This is the function I have been trying:
    Code:
    CummulativeTotal: DSum([countofagent],"BeyondDeliveryDays","[AGENT] = '" & [Agent] & "'" And [DeliveryDaysBeyondCommitment]<=" & [DeliveryDaysBeyondCommitment]&"")

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,848
    Recommend you build a report and do the aggregate calcs there. Use Grouping & Sorting to organize records and then textboxes in group and report header/footer for sums. Then reference those textboxes in percent calc. Textbox on report has a RunningSum property that can be used to do the cumulative percent calc.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17
    I want to build a graph on a report off of this data.

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,848
    Then options:

    1. build aggregate query, join it to the original dataset to make the aggregate sum available to every record

    2. use Domain Aggregate function - DSum
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17
    I tried that above, but I can't get the forumula to work correctly.

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,848
    Show what you attempted and/or provide db for analysis. Follow instructions at bottom of my post.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    tomnsd is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    17
    Here is the function:

    Code:
    CummulativeTotal: DSum([countofagent],"BeyondDeliveryDays","[AGENT] = '" & [Agent] & "'" And [DeliveryDaysBeyondCommitment]<=" & [DeliveryDaysBeyondCommitment]&"")
    The tables and fields are listed above.
    Thanks.

  8. #8
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    29,848
    [countofagent] field must be within quote marks

    extra quote mark before the And operator

    the concatenation of empty string at the end is not necessary, although shouldn't hurt
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

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

Similar Threads

  1. Replies: 3
    Last Post: 09-06-2012, 02:35 PM
  2. Running Total (Cumulative)
    By jamesborne in forum Queries
    Replies: 3
    Last Post: 12-26-2011, 07:30 PM
  3. Cumulative Percentage and dsum
    By treyxman in forum Queries
    Replies: 1
    Last Post: 08-11-2011, 07:31 AM
  4. cumulative total
    By afshin in forum Queries
    Replies: 14
    Last Post: 08-11-2011, 12:42 AM
  5. Cumulative total in query
    By MikeWaring in forum Queries
    Replies: 2
    Last Post: 12-18-2010, 11:40 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
  •  
Tech Forums: Microsoft Office Forums