My running sum didn't work

[forcefield via AccessMonster.com]

HI everyone
Can someone please correct my running sum?
I have a query1 with the following fields: Item, Description, FQty

When I run query2
SELECT Query1.Item, Query1.Description, Query1.FQty, DSum("FQty","Query1",
"Item & Description= '" & [Item] & [Description] & "'") AS RunSum
FROM Query1
ORDER BY Query1.Item, Query1.Description;

The result for Query2 was

Item Description Qty RunSum
Coloring Bottles 16 16
Flour Bags 12 22
Flour Bags 10 22
Oil Bottles 14 40
Oil Bottles 14 40
Oil Bottles 12 40
Peanut Bags 5 5
Sauce Bottles 5 5

What I would like to see is
Query2
Item Description Qty RunSum
Coloring Bottles 16 16
Flour Bags 12 12
Flour Bags 10 22
Oil Bottles 14 14
Oil Bottles 14 28
Oil Bottles 12 40
Peanut Bags 5 5
Sauce Bottles 5 5


Thanks for your help

 

[Baz]

Your DSum function is doing what you have "told" it to do: it is totalling
all the FQty for each specified item and description. There is no way for
it to "know" that you wanted a running sum. Indeed, based on this data,
there is no way to do what you want, since there is no way of telling one,
say, Oil/Bottles row from any of the others. To do what you want you would
need another column in Query1's results which is not only unique for each
result, *but is in the correct sort order for doing the running sum*.

However, SQL is really not appropriate for doing running sums. It's the
sort of thing that should be applied to a result set by a client application
e.g. an Access report, or Excel.

 

[John Spencer]

Without some field (or fields) to specify a unique order of records there is
no way to do this in a query. Your data (as posted) does not have any such
field. However, if you want a running sum on a report it is simple. Just
use your first query as the source for the report.

Add controls for Item, Description, and Qty and assign the fields to the
controls
Add another control and assign qty to it and set the Running Sum property to
OVER GROUP
Select View: Sorting and Grouping from the menu
Enter Item as Field to sort by and set Group Header to Yes
Close the Sorting and grouping dialog
Set the group header height to zero or set the visible property of the group
header to No

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[forcefield via AccessMonster.com]

Hi John,
Thanks for the reply. Actually the data provided is only a sample as I do not
want to complicate the problem. I intended to export a query consisting of
even more fields to Excel.
In other words, am I correct to say I need another unique field (on the where
condition) for the query on running sum to work?

Thanks

Without some field (or fields) to specify a unique order of records there is
no way to do this in a query. Your data (as posted) does not have any such
field. However, if you want a running sum on a report it is simple. Just
use your first query as the source for the report.

Add controls for Item, Description, and Qty and assign the fields to the
controls
Add another control and assign qty to it and set the Running Sum property to
OVER GROUP
Select View: Sorting and Grouping from the menu
Enter Item as Field to sort by and set Group Header to Yes
Close the Sorting and grouping dialog
Set the group header height to zero or set the visible property of the group
header to No

HI everyone
Can someone please correct my running sum?

[quoted text clipped - 31 lines]

Thanks for your help

 

[Baz]

You need a unique field returned by Query1 and by which the Query1 results
can be sorted in the correct order. Let's suppose that it is called
"sum_order". Then Query2 would look something like this:

SELECT Query1.Item, Query1.Description, Query1.FQty, DSum("FQty","Query1",
"Item & Description= '" & [Item] & [Description] & "' AND sum_order <= " &
Query1.sum_order) AS RunSum
FROM Query1
ORDER BY Query1.Item, Query1.Description;

However, it's an ugly query. I think I would add the running sum once the
data is in Excel.

Hi John,
Thanks for the reply. Actually the data provided is only a sample as I do not
want to complicate the problem. I intended to export a query consisting of
even more fields to Excel.
In other words, am I correct to say I need another unique field (on the where
condition) for the query on running sum to work?

Thanks

Without some field (or fields) to specify a unique order of records there is
no way to do this in a query. Your data (as posted) does not have any such
field. However, if you want a running sum on a report it is simple. Just
use your first query as the source for the report.

Add controls for Item, Description, and Qty and assign the fields to the
controls
Add another control and assign qty to it and set the Running Sum property to
OVER GROUP
Select View: Sorting and Grouping from the menu
Enter Item as Field to sort by and set Group Header to Yes
Close the Sorting and grouping dialog
Set the group header height to zero or set the visible property of the group
header to No

HI everyone
Can someone please correct my running sum?

[quoted text clipped - 31 lines]

Thanks for your help

 

[Michel Walsh]

Try:

SELECT Query1.Item, Query1.Description, Query1.FQty, DSum("FQty","Query1",
"Item & Description >= '" & [Item] & [Description] & "'") AS RunSum
FROM Query1
ORDER BY Query1.Item, Query1.Description;



That could be slow, though, due to the large amount of strings your code has
to create and to compare.



SELECT a.item, a.description, SUM(a.qty), SUM(b.qty) As RunSum
FROM query1 AS a INNER JOIN query1 AS b
ON a.item>b.item OR ( a.item=b.item AND a.description >= b.description)
ORDER BY a.item, a.description



could be a little bit faster.



Vanderghast, Access MVP

 

[forcefield via AccessMonster.com]

Thanks you Baz and Michel for your immense help. I certainly would try both
methods.

Try:

SELECT Query1.Item, Query1.Description, Query1.FQty, DSum("FQty","Query1",
"Item & Description >= '" & [Item] & [Description] & "'") AS RunSum
FROM Query1
ORDER BY Query1.Item, Query1.Description;

That could be slow, though, due to the large amount of strings your code has
to create and to compare.

SELECT a.item, a.description, SUM(a.qty), SUM(b.qty) As RunSum
FROM query1 AS a INNER JOIN query1 AS b
ON a.item>b.item OR ( a.item=b.item AND a.description >= b.description)
ORDER BY a.item, a.description

could be a little bit faster.

Vanderghast, Access MVP

HI everyone
Can someone please correct my running sum?

[quoted text clipped - 31 lines]

Thanks for your help

 

[Michel Walsh]

Indeed, the third argument in the DSum should be a criteria that uniquely
identifies each record:


DSum("FQty","Query1", "Item & Description >= '" & [Item] & [Description] &
"'")

must be replace by something like:

DSum("FQty","Query1", "Item & Description & DateTimeStamp >= '" & [Item] &
[Description] & [DateTimeStamp] "'")


where the assumed field DateTimeStamp is a field that breaks any ambiguity
about which record is actually considered, given Item and Description.


Vanderghast, Access MVP

I'm afraid Michel's method will not work. He has overlooked the fact
that,
in your Query1 results, the Item and Description are repeating, and that
you
only want the running sum within each Item/Description group.

Thanks you Baz and Michel for your immense help. I certainly would try both
methods.

Try:

SELECT Query1.Item, Query1.Description, Query1.FQty, DSum("FQty","Query1",
"Item & Description >= '" & [Item] & [Description] & "'") AS RunSum
FROM Query1
ORDER BY Query1.Item, Query1.Description;

That could be slow, though, due to the large amount of strings your code has
to create and to compare.

SELECT a.item, a.description, SUM(a.qty), SUM(b.qty) As RunSum
FROM query1 AS a INNER JOIN query1 AS b
ON a.item>b.item OR ( a.item=b.item AND a.description >= b.description)
ORDER BY a.item, a.description

could be a little bit faster.

Vanderghast, Access MVP

HI everyone
Can someone please correct my running sum?
[quoted text clipped - 31 lines]

Thanks for your help