Running Sum in a Query

[NEWER USER]

I am struggling with the DSum function in a query. I have 4 fields and want
to calculate a Running Sum in the last column of the query.

Number Group Subgroup Pct RunSum
ABC123 115 500 1.022
ABC345 115 500 .052
ABC765 115 500 .234

The numbers in the Number field are always unique(no duplicates). The
Group/Subgroup fields are always the same as I am using parameter values to
filter the search when running query. Can anyone give me some code to
perform the RunSum calculation? Any help greatly appreciated. Thank you.

 

[Allen Browne]

You could do this with a subquery to sum the Pct field.

This example assumes your Number field is the primary key:

SELECT Table1.*,
(SELECT Sum(Pct) AS SumOfPct
FROM Table1 AS Dupe
WHERE Dupe.[Number] <= Table1.[Number]) AS RunSum
FROM Table1;

You need to modify that if the query has other criterie, or is sorted
differently.

For an introduction to subqueries, see:
http://allenbrowne.com/subquery-01.html#Aggregation

If you prefer to use DSum:
DSum("Pct", "Table1", "[Number] <= " & [Number])

BTW, Number and Group are reserved words, so not good names for fields. For
a list of names to avoid when designing tables, refer to:
http://allenbrowne.com/AppIssueBadWord.html

 

[NEWER USER]

Still struggling with the DSum function. The Number field is not a primary
key field. Product ID is the Autonumber/Primary Key field. I substituted
ProductID in place of Number and still get a #Error when running the query.
I added another column titled Sales and sorted in descending order as well as
replacing Number with Sales and no success either. I tried:

SELECT tblProduct.Sales, tblProduct.Number, qryTEST.Group, qryTEST.Subgroup,
[Sales]/[SumOfSales]*100 AS Pct, DSum("Pct","qryTEST1","[Number] <= " &
[Number]) AS RunSum
FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID
WHERE (((qryTEST.Group)=[Enter Group Number]) AND ((qryTEST.Subgroup)=[Enter
Subgroup Number]))
ORDER BY tblProduct.Sales DESC;

ALSO tried:

SELECT tblProduct.Sales, tblProduct.Number, qryTEST.Group, qryTEST.Subgroup,
[Sales]/[SumOfSales]*100 AS Pct, DSum("Pct","qryTEST1","[Sales] <= " &
[Sales]) AS RunSum
FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID
WHERE (((qryTEST.Group)=[Enter Group Number]) AND ((qryTEST.Subgroup)=[Enter
Subgroup Number]))
ORDER BY tblProduct.Sales DESC;

#Error message in the RunSum Column. Where am I going wrong?

You could do this with a subquery to sum the Pct field.

This example assumes your Number field is the primary key:

SELECT Table1.*,
(SELECT Sum(Pct) AS SumOfPct
FROM Table1 AS Dupe
WHERE Dupe.[Number] <= Table1.[Number]) AS RunSum
FROM Table1;

You need to modify that if the query has other criterie, or is sorted
differently.

For an introduction to subqueries, see:
http://allenbrowne.com/subquery-01.html#Aggregation

If you prefer to use DSum:
DSum("Pct", "Table1", "[Number] <= " & [Number])

BTW, Number and Group are reserved words, so not good names for fields. For
a list of names to avoid when designing tables, refer to:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am struggling with the DSum function in a query. I have 4 fields and
want
to calculate a Running Sum in the last column of the query.

Number Group Subgroup Pct RunSum
ABC123 115 500 1.022
ABC345 115 500 .052
ABC765 115 500 .234

The numbers in the Number field are always unique(no duplicates). The
Group/Subgroup fields are always the same as I am using parameter values
to
filter the search when running query. Can anyone give me some code to
perform the RunSum calculation? Any help greatly appreciated. Thank you.

 

[Allen Browne]

Is Number a Number field?
If it's text, you need extra quotes:
DSum("Pct","qryTEST1","[Number] <= """ &
[Number] & """") AS RunSum
Note that Text field sort character-by-characters, so may not give the
answer you expect.

If it is number, it could foul up when null. Try:
DSum("Pct","qryTEST1","[Number] <= " &
Nz([Number], 0)) AS RunSum

If nothing works, the fact that NUMBER is a reserved word could be where the
query is failing. Adding the table name in front of it might help, e.g.:
[qryTEST1].[Number]

Also, since Number is not the primary key, it may not be unique, and could
even be Null. This won't cause the error, but it could yield inaccurate
answers.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Still struggling with the DSum function. The Number field is not a
primary
key field. Product ID is the Autonumber/Primary Key field. I substituted
ProductID in place of Number and still get a #Error when running the
query.
I added another column titled Sales and sorted in descending order as well
as
replacing Number with Sales and no success either. I tried:

SELECT tblProduct.Sales, tblProduct.Number, qryTEST.Group,
qryTEST.Subgroup,
[Sales]/[SumOfSales]*100 AS Pct, DSum("Pct","qryTEST1","[Number] <= " &
[Number]) AS RunSum
FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID
WHERE (((qryTEST.Group)=[Enter Group Number]) AND
((qryTEST.Subgroup)=[Enter
Subgroup Number]))
ORDER BY tblProduct.Sales DESC;

ALSO tried:

SELECT tblProduct.Sales, tblProduct.Number, qryTEST.Group,
qryTEST.Subgroup,
[Sales]/[SumOfSales]*100 AS Pct, DSum("Pct","qryTEST1","[Sales] <= " &
[Sales]) AS RunSum
FROM qryTEST INNER JOIN tblProduct ON qryTEST.GroupID = tblProduct.GroupID
WHERE (((qryTEST.Group)=[Enter Group Number]) AND
((qryTEST.Subgroup)=[Enter
Subgroup Number]))
ORDER BY tblProduct.Sales DESC;

#Error message in the RunSum Column. Where am I going wrong?

You could do this with a subquery to sum the Pct field.

This example assumes your Number field is the primary key:

SELECT Table1.*,
(SELECT Sum(Pct) AS SumOfPct
FROM Table1 AS Dupe
WHERE Dupe.[Number] <= Table1.[Number]) AS RunSum
FROM Table1;

You need to modify that if the query has other criterie, or is sorted
differently.

For an introduction to subqueries, see:
http://allenbrowne.com/subquery-01.html#Aggregation

If you prefer to use DSum:
DSum("Pct", "Table1", "[Number] <= " & [Number])

BTW, Number and Group are reserved words, so not good names for fields.
For
a list of names to avoid when designing tables, refer to:
http://allenbrowne.com/AppIssueBadWord.html

I am struggling with the DSum function in a query. I have 4 fields and
want
to calculate a Running Sum in the last column of the query.

Number Group Subgroup Pct RunSum
ABC123 115 500 1.022
ABC345 115 500 .052
ABC765 115 500 .234

The numbers in the Number field are always unique(no duplicates). The
Group/Subgroup fields are always the same as I am using parameter
values
to
filter the search when running query. Can anyone give me some code to
perform the RunSum calculation? Any help greatly appreciated. Thank
you.