Using dsum to return a cumulative total

[tomnsd]

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:

CummulativeTotal: DSum([countofagent],"BeyondDeliveryDays","[AGENT] = '" & [Agent] & "'" And [DeliveryDaysBeyondCommitment]<=" & [DeliveryDaysBeyondCommitment]&"")

[June7]

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.

[tomnsd]

I want to build a graph on a report off of this data.

[June7]

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

[tomnsd]

I tried that above, but I can't get the forumula to work correctly.

[June7]

Show what you attempted and/or provide db for analysis. Follow instructions at bottom of my post.

[tomnsd]

Here is the function:

CummulativeTotal: DSum([countofagent],"BeyondDeliveryDays","[AGENT] = '" & [Agent] & "'" And [DeliveryDaysBeyondCommitment]<=" & [DeliveryDaysBeyondCommitment]&"")

The tables and fields are listed above.
Thanks.

[June7]

[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