Running Sums in a query

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Is ther any way to do a running sum in a query? I can, of
course, do this in a report, but I would like to do this
as part of an inventory check in a table or query.

Thanks to anyone who solves this for me,
Bill
 
Dear Bill:

A running sum can be performed in a query using a subquery. This must
be based on a sorted order of the rows in that query. The running sum
can be over all the rows in the query, or it can start over in a
"group", being defined as subsets of rows in which one or more columns
do not change.

For a simple case of a running sum over an entire query resutls set:

SELECT Sort1, (SELECT SUM(Value) FROM YourTable T2
WHERE T2.Sort1 <= T1.Sort1) AS RunningSum
FROM YourTable T1
ORDER BY Sort1

T1 and T2 are aliasses for two independent references to the same
table.

If you need a more complex running sum I could help with it here.
Please post a query of your situation, omitting the running sum. It
must include an ORDER BY clause putting the rows in the sequence you
want them summed. If you want the sum to restart based on grouping,
please state the columns on which it is to be grouped. These columns
also should be the first columns of the ORDER BY clause.

From that I can likely create the running sum subquery necessary.

One note on the ordering. If it is not unique, the subquery will add
the new values for all the rows where the ordering is identical
simultaneously. The query will present these rows one at a time, but
the running sum will not be able to distinguish between them. If you
add one or more additional key fields till the ordering is unique this
can be eliminated. When the set of ordering fields is not unique, the
ordering among rows having duplicate ordering is arbitrary, causing
this potential problem.

In large databases this can be a bit slow in some cases.

Is ther any way to do a running sum in a query? I can, of
course, do this in a report, but I would like to do this
as part of an inventory check in a table or query.

Thanks to anyone who solves this for me,
Bill
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I am running a selective running sum on an inventroy
database. Therefore, I have an "on-hand" which needs to
be reduced by consumption for each item.

Here's the SQL I've been playing with. My table name is
wwwMASTER for WO sort. NewInv is just a copy of Inventory

SELECT [wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
for WO sort].ItemProductCode AS IPCalias, [wwwMASTER for
WO sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory AS NewInv, DSum([Inventory],"wwwMASTER for
WO sort",[ItemProductCode]=[IPCalias]) AS InvSoFar,
[wwwMASTER for WO sort].InventoryUnit, [wwwMASTER for WO
sort].OrderDesc, [wwwMASTER for WO sort].CustomerID,
[wwwMASTER for WO sort].CustomerPO, [wwwMASTER for WO
sort].ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].ItemProductCode, [wwwMASTER for WO
sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory, [wwwMASTER for WO sort].InventoryUnit,
[wwwMASTER for WO sort].OrderDesc, [wwwMASTER for WO
sort].CustomerID, [wwwMASTER for WO sort].CustomerPO,
[wwwMASTER for WO sort].ProductCode
ORDER BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].Source;

This is close. The I'm using inventory to calculate
InvSoFar, but the calulation is giving me some strange #'s.

Bill
 
Tom,

Looking at the SQL I sent, it looks like the criteria is
being used as the calculation. Not sure how to fix. Any
suggestions you might have would be appreciated. It's in
the DSUM area.

The ItemProductCode must be equivalent to the IPC alias
for the inventory adjustment to occur.

Thanks,
Bill
 
Dear Bill:

For my own reference so I can study it easier, here's you query in
simplified form:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
DSum(Inventory, "wwwMASTER for WO sort", ItemProductCode =
IPCalias) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

To get what I THINK you're after, try this:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
(SELECT SUM(Inventory) FROM [wwwMASTER for WO Sort] T2
WHERE T2.ItemProductCode < T1.ItemProductCode
OR (T2.ItemProductCode = T1.ItemProductCode
AND T2.Source <= T1.Source)) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort] T1
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

If you want to study how this works (assuming it does, for I cannot
test it against your database from here) you may want to study topics
such as:

- alias
- correlated subqueries

There is a potential problem. If ItemProductCode / Source (the
columns by which you sort) are not unique (that is, if more than one
row may have the same values of these two columns) then the Inventory
for those two (or more) rows will all add into the running sum
simultaneously. If so, and if you can pick one or more additional
columns by which to order the results so that it is unique, the
subquery can be modified to reflect this, and your running sum will
then add just one row at a time.

Please let me know if this helped, and if I can be of any other
assistance in this matter.

Tom,

I am running a selective running sum on an inventroy
database. Therefore, I have an "on-hand" which needs to
be reduced by consumption for each item.

Here's the SQL I've been playing with. My table name is
wwwMASTER for WO sort. NewInv is just a copy of Inventory

SELECT [wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
for WO sort].ItemProductCode AS IPCalias, [wwwMASTER for
WO sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory AS NewInv, DSum([Inventory],"wwwMASTER for
WO sort",[ItemProductCode]=[IPCalias]) AS InvSoFar,
[wwwMASTER for WO sort].InventoryUnit, [wwwMASTER for WO
sort].OrderDesc, [wwwMASTER for WO sort].CustomerID,
[wwwMASTER for WO sort].CustomerPO, [wwwMASTER for WO
sort].ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].ItemProductCode, [wwwMASTER for WO
sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory, [wwwMASTER for WO sort].InventoryUnit,
[wwwMASTER for WO sort].OrderDesc, [wwwMASTER for WO
sort].CustomerID, [wwwMASTER for WO sort].CustomerPO,
[wwwMASTER for WO sort].ProductCode
ORDER BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].Source;

This is close. The I'm using inventory to calculate
InvSoFar, but the calulation is giving me some strange #'s.

Bill
-----Original Message-----
Dear Bill:

A running sum can be performed in a query using a subquery. This must
be based on a sorted order of the rows in that query. The running sum
can be over all the rows in the query, or it can start over in a
"group", being defined as subsets of rows in which one or more columns
do not change.

For a simple case of a running sum over an entire query resutls set:

SELECT Sort1, (SELECT SUM(Value) FROM YourTable T2
WHERE T2.Sort1 <= T1.Sort1) AS RunningSum
FROM YourTable T1
ORDER BY Sort1

T1 and T2 are aliasses for two independent references to the same
table.

If you need a more complex running sum I could help with it here.
Please post a query of your situation, omitting the running sum. It
must include an ORDER BY clause putting the rows in the sequence you
want them summed. If you want the sum to restart based on grouping,
please state the columns on which it is to be grouped. These columns
also should be the first columns of the ORDER BY clause.

From that I can likely create the running sum subquery necessary.

One note on the ordering. If it is not unique, the subquery will add
the new values for all the rows where the ordering is identical
simultaneously. The query will present these rows one at a time, but
the running sum will not be able to distinguish between them. If you
add one or more additional key fields till the ordering is unique this
can be eliminated. When the set of ordering fields is not unique, the
ordering among rows having duplicate ordering is arbitrary, causing
this potential problem.

In large databases this can be a bit slow in some cases.



Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I think I am VERY close. BUT, I get a syntax error in the
GROUP BY Clause. I think it is because of the absence of
InvSoFar or a missing "(" or ",".

My SQL after some simplification is:

SELECT [wwwMASTER for WO Sort].ItemProductCode, [wwwMASTER
for WO Sort].ProductName, [wwwMASTER for WO Sort].Source,
[wwwMASTER for WO Sort].Inventory, [wwwMASTER for WO
Sort].InventoryUnit,

(SELECT SUM(Inventory) FROM wwwMASTER for WO sort_1
WHERE [wwwMASTER for WO sort_1].ItemProductCode <
[wwwMASTER for WO sort].ItemProductCode
OR [wwwMASTER for WO sort_1].ItemProductCode =
[wwwMASTER for WO sort].ItemProductCode
AND [wwwMASTER for WO sort_1].Source <= [wwwMASTER
for WO sort].Source) AS InvSoFar,

[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode

FROM [wwwMASTER for WO Sort]

GROUP BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].ProductName, [wwwMASTER for WO
Sort].Source, [wwwMASTER for WO Sort].Inventory,
[wwwMASTER for WO Sort].InventoryUnit, InvSoFar,
[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode,

ORDER BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].Source;

Bill
-----Original Message-----
Dear Bill:

For my own reference so I can study it easier, here's you query in
simplified form:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
DSum(Inventory, "wwwMASTER for WO sort", ItemProductCode =
IPCalias) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

To get what I THINK you're after, try this:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
(SELECT SUM(Inventory) FROM [wwwMASTER for WO Sort] T2
WHERE T2.ItemProductCode < T1.ItemProductCode
OR (T2.ItemProductCode = T1.ItemProductCode
AND T2.Source <= T1.Source)) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort] T1
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

If you want to study how this works (assuming it does, for I cannot
test it against your database from here) you may want to study topics
such as:

- alias
- correlated subqueries

There is a potential problem. If ItemProductCode / Source (the
columns by which you sort) are not unique (that is, if more than one
row may have the same values of these two columns) then the Inventory
for those two (or more) rows will all add into the running sum
simultaneously. If so, and if you can pick one or more additional
columns by which to order the results so that it is unique, the
subquery can be modified to reflect this, and your running sum will
then add just one row at a time.

Please let me know if this helped, and if I can be of any other
assistance in this matter.

Tom,

I am running a selective running sum on an inventroy
database. Therefore, I have an "on-hand" which needs to
be reduced by consumption for each item.

Here's the SQL I've been playing with. My table name is
wwwMASTER for WO sort. NewInv is just a copy of Inventory

SELECT [wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
for WO sort].ItemProductCode AS IPCalias, [wwwMASTER for
WO sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory AS NewInv, DSum([Inventory],"wwwMASTER for
WO sort",[ItemProductCode]=[IPCalias]) AS InvSoFar,
[wwwMASTER for WO sort].InventoryUnit, [wwwMASTER for WO
sort].OrderDesc, [wwwMASTER for WO sort].CustomerID,
[wwwMASTER for WO sort].CustomerPO, [wwwMASTER for WO
sort].ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].ItemProductCode, [wwwMASTER for WO
sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory, [wwwMASTER for WO sort].InventoryUnit,
[wwwMASTER for WO sort].OrderDesc, [wwwMASTER for WO
sort].CustomerID, [wwwMASTER for WO sort].CustomerPO,
[wwwMASTER for WO sort].ProductCode
ORDER BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].Source;

This is close. The I'm using inventory to calculate
InvSoFar, but the calulation is giving me some strange #'s.

Bill
-----Original Message-----
Dear Bill:

A running sum can be performed in a query using a subquery. This must
be based on a sorted order of the rows in that query. The running sum
can be over all the rows in the query, or it can start over in a
"group", being defined as subsets of rows in which one
Click to expand...
or
more columns
do not change.

For a simple case of a running sum over an entire query resutls set:

SELECT Sort1, (SELECT SUM(Value) FROM YourTable T2
WHERE T2.Sort1 <= T1.Sort1) AS RunningSum
FROM YourTable T1
ORDER BY Sort1

T1 and T2 are aliasses for two independent references
Click to expand...
to
the same
table.

If you need a more complex running sum I could help
Click to expand...
with
it here.
Please post a query of your situation, omitting the running sum. It
must include an ORDER BY clause putting the rows in the sequence you
want them summed. If you want the sum to restart based on grouping,
please state the columns on which it is to be grouped. These columns
also should be the first columns of the ORDER BY clause.

From that I can likely create the running sum subquery necessary.

One note on the ordering. If it is not unique, the subquery will add
the new values for all the rows where the ordering is identical
simultaneously. The query will present these rows one
Click to expand...
at
a time, but
the running sum will not be able to distinguish between them. If you
add one or more additional key fields till the ordering is unique this
can be eliminated. When the set of ordering fields is not unique, the
ordering among rows having duplicate ordering is arbitrary, causing
this potential problem.

In large databases this can be a bit slow in some cases.
Click to expand...
wrote:

Is ther any way to do a running sum in a query? I
Click to expand...
can,
of
course, do this in a report, but I would like to do this
as part of an inventory check in a table or query.

Thanks to anyone who solves this for me,
Bill

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
 
Dear Bill:

I'm thinking what you probably want is to drop by GROUP BY altogether,
unless you're after some effect about which I don't know. Are you
wanting to show each row from the source table, or are you actually
wanting to group several of them together?

Tom,

I think I am VERY close. BUT, I get a syntax error in the
GROUP BY Clause. I think it is because of the absence of
InvSoFar or a missing "(" or ",".

My SQL after some simplification is:

SELECT [wwwMASTER for WO Sort].ItemProductCode, [wwwMASTER
for WO Sort].ProductName, [wwwMASTER for WO Sort].Source,
[wwwMASTER for WO Sort].Inventory, [wwwMASTER for WO
Sort].InventoryUnit,

(SELECT SUM(Inventory) FROM wwwMASTER for WO sort_1
WHERE [wwwMASTER for WO sort_1].ItemProductCode <
[wwwMASTER for WO sort].ItemProductCode
OR [wwwMASTER for WO sort_1].ItemProductCode =
[wwwMASTER for WO sort].ItemProductCode
AND [wwwMASTER for WO sort_1].Source <= [wwwMASTER
for WO sort].Source) AS InvSoFar,

[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode

FROM [wwwMASTER for WO Sort]

GROUP BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].ProductName, [wwwMASTER for WO
Sort].Source, [wwwMASTER for WO Sort].Inventory,
[wwwMASTER for WO Sort].InventoryUnit, InvSoFar,
[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode,

ORDER BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].Source;

Bill
-----Original Message-----
Dear Bill:

For my own reference so I can study it easier, here's you query in
simplified form:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
DSum(Inventory, "wwwMASTER for WO sort", ItemProductCode =
IPCalias) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

To get what I THINK you're after, try this:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
(SELECT SUM(Inventory) FROM [wwwMASTER for WO Sort] T2
WHERE T2.ItemProductCode < T1.ItemProductCode
OR (T2.ItemProductCode = T1.ItemProductCode
AND T2.Source <= T1.Source)) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort] T1
GROUP BY ItemProductCode, ItemProductCode, ProductName, Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

If you want to study how this works (assuming it does, for I cannot
test it against your database from here) you may want to study topics
such as:

- alias
- correlated subqueries

There is a potential problem. If ItemProductCode / Source (the
columns by which you sort) are not unique (that is, if more than one
row may have the same values of these two columns) then the Inventory
for those two (or more) rows will all add into the running sum
simultaneously. If so, and if you can pick one or more additional
columns by which to order the results so that it is unique, the
subquery can be modified to reflect this, and your running sum will
then add just one row at a time.

Please let me know if this helped, and if I can be of any other
assistance in this matter.

Tom,

I am running a selective running sum on an inventroy
database. Therefore, I have an "on-hand" which needs to
be reduced by consumption for each item.

Here's the SQL I've been playing with. My table name is
wwwMASTER for WO sort. NewInv is just a copy of Inventory

SELECT [wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
for WO sort].ItemProductCode AS IPCalias, [wwwMASTER for
WO sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory AS NewInv, DSum([Inventory],"wwwMASTER for
WO sort",[ItemProductCode]=[IPCalias]) AS InvSoFar,
[wwwMASTER for WO sort].InventoryUnit, [wwwMASTER for WO
sort].OrderDesc, [wwwMASTER for WO sort].CustomerID,
[wwwMASTER for WO sort].CustomerPO, [wwwMASTER for WO
sort].ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].ItemProductCode, [wwwMASTER for WO
sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory, [wwwMASTER for WO sort].InventoryUnit,
[wwwMASTER for WO sort].OrderDesc, [wwwMASTER for WO
sort].CustomerID, [wwwMASTER for WO sort].CustomerPO,
[wwwMASTER for WO sort].ProductCode
ORDER BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].Source;

This is close. The I'm using inventory to calculate
InvSoFar, but the calulation is giving me some strange #'s.

Bill

-----Original Message-----
Dear Bill:

A running sum can be performed in a query using a
subquery. This must
be based on a sorted order of the rows in that query.
The running sum
can be over all the rows in the query, or it can start
over in a
"group", being defined as subsets of rows in which one or
more columns
do not change.

For a simple case of a running sum over an entire query
resutls set:

SELECT Sort1, (SELECT SUM(Value) FROM YourTable T2
WHERE T2.Sort1 <= T1.Sort1) AS RunningSum
FROM YourTable T1
ORDER BY Sort1

T1 and T2 are aliasses for two independent references to
the same
table.

If you need a more complex running sum I could help with
it here.
Please post a query of your situation, omitting the
running sum. It
must include an ORDER BY clause putting the rows in the
sequence you
want them summed. If you want the sum to restart based
on grouping,
please state the columns on which it is to be grouped.
These columns
also should be the first columns of the ORDER BY clause.

From that I can likely create the running sum subquery
necessary.

One note on the ordering. If it is not unique, the
subquery will add
the new values for all the rows where the ordering is
identical
simultaneously. The query will present these rows one at
a time, but
the running sum will not be able to distinguish between
them. If you
add one or more additional key fields till the ordering
is unique this
can be eliminated. When the set of ordering fields is
not unique, the
ordering among rows having duplicate ordering is
arbitrary, causing
this potential problem.

In large databases this can be a bit slow in some cases.

On Sun, 28 Sep 2003 07:44:13 -0700, "Bill"
<[email protected]>
wrote:

Is ther any way to do a running sum in a query? I can,
of
course, do this in a report, but I would like to do this
as part of an inventory check in a table or query.

Thanks to anyone who solves this for me,
Bill

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

I want to show each row but I only need to group by 1 item-
-ItemProductCode

Bill

-----Original Message-----
Dear Bill:

I'm thinking what you probably want is to drop by GROUP BY altogether,
unless you're after some effect about which I don't know. Are you
wanting to show each row from the source table, or are you actually
wanting to group several of them together?

Tom,

I think I am VERY close. BUT, I get a syntax error in the
GROUP BY Clause. I think it is because of the absence of
InvSoFar or a missing "(" or ",".

My SQL after some simplification is:

SELECT [wwwMASTER for WO Sort].ItemProductCode, [wwwMASTER
for WO Sort].ProductName, [wwwMASTER for WO Sort].Source,
[wwwMASTER for WO Sort].Inventory, [wwwMASTER for WO
Sort].InventoryUnit,

(SELECT SUM(Inventory) FROM wwwMASTER for WO sort_1
WHERE [wwwMASTER for WO sort_1].ItemProductCode <
[wwwMASTER for WO sort].ItemProductCode
OR [wwwMASTER for WO sort_1].ItemProductCode =
[wwwMASTER for WO sort].ItemProductCode
AND [wwwMASTER for WO sort_1].Source <= [wwwMASTER
for WO sort].Source) AS InvSoFar,

[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode

FROM [wwwMASTER for WO Sort]

GROUP BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].ProductName, [wwwMASTER for WO
Sort].Source, [wwwMASTER for WO Sort].Inventory,
[wwwMASTER for WO Sort].InventoryUnit, InvSoFar,
[wwwMASTER for WO Sort].OrderDesc, [wwwMASTER for WO
Sort].CustomerID, [wwwMASTER for WO Sort].CustomerPO,
[wwwMASTER for WO Sort].ProductCode,

ORDER BY [wwwMASTER for WO Sort].ItemProductCode,
[wwwMASTER for WO Sort].Source;

Bill
-----Original Message-----
Dear Bill:

For my own reference so I can study it easier, here's
Click to expand...
you
query in
simplified form:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
DSum(Inventory, "wwwMASTER for WO sort", ItemProductCode =
IPCalias) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY ItemProductCode, ItemProductCode,
Click to expand...
ProductName,
Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

To get what I THINK you're after, try this:

SELECT ItemProductCode, ItemProductCode AS IPCalias, ProductName,
Source, Inventory, Inventory AS NewInv,
(SELECT SUM(Inventory) FROM [wwwMASTER for WO Sort] T2
WHERE T2.ItemProductCode < T1.ItemProductCode
OR (T2.ItemProductCode = T1.ItemProductCode
AND T2.Source <= T1.Source)) AS InvSoFar,
InventoryUnit, OrderDesc, CustomerID, CustomerPO, ProductCode
FROM [wwwMASTER for WO sort] T1
GROUP BY ItemProductCode, ItemProductCode,
Click to expand...
ProductName,
Source,
Inventory, InventoryUnit, OrderDesc, CustomerID, CustomerPO,
ProductCode
ORDER BY ItemProductCode, Source;

If you want to study how this works (assuming it does, for I cannot
test it against your database from here) you may want
Click to expand...
to
study topics
such as:

- alias
- correlated subqueries

There is a potential problem. If ItemProductCode / Source (the
columns by which you sort) are not unique (that is, if more than one
row may have the same values of these two columns) then the Inventory
for those two (or more) rows will all add into the running sum
simultaneously. If so, and if you can pick one or more additional
columns by which to order the results so that it is unique, the
subquery can be modified to reflect this, and your running sum will
then add just one row at a time.

Please let me know if this helped, and if I can be of
Click to expand...
any
other
assistance in this matter.
Click to expand...
wrote:

Tom,

I am running a selective running sum on an inventroy
database. Therefore, I have an "on-hand" which needs to
be reduced by consumption for each item.

Here's the SQL I've been playing with. My table name is
wwwMASTER for WO sort. NewInv is just a copy of Inventory

SELECT [wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
for WO sort].ItemProductCode AS IPCalias, [wwwMASTER for
WO sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory AS NewInv, DSum([Inventory],"wwwMASTER for
WO sort",[ItemProductCode]=[IPCalias]) AS InvSoFar,
[wwwMASTER for WO sort].InventoryUnit, [wwwMASTER for WO
sort].OrderDesc, [wwwMASTER for WO sort].CustomerID,
[wwwMASTER for WO sort].CustomerPO, [wwwMASTER for WO
sort].ProductCode
FROM [wwwMASTER for WO sort]
GROUP BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].ItemProductCode, [wwwMASTER
Click to expand...
for
WO
sort].ProductName, [wwwMASTER for WO sort].Source,
[wwwMASTER for WO sort].Inventory, [wwwMASTER for WO
sort].Inventory, [wwwMASTER for WO sort].InventoryUnit,
[wwwMASTER for WO sort].OrderDesc, [wwwMASTER for WO
sort].CustomerID, [wwwMASTER for WO sort].CustomerPO,
[wwwMASTER for WO sort].ProductCode
ORDER BY [wwwMASTER for WO sort].ItemProductCode,
[wwwMASTER for WO sort].Source;

This is close. The I'm using inventory to calculate
InvSoFar, but the calulation is giving me some strange #'s.

Bill

-----Original Message-----
Dear Bill:

A running sum can be performed in a query using a
subquery. This must
be based on a sorted order of the rows in that query.
The running sum
can be over all the rows in the query, or it can start
over in a
"group", being defined as subsets of rows in which
Click to expand...
one
or
more columns
do not change.

For a simple case of a running sum over an entire query
resutls set:

SELECT Sort1, (SELECT SUM(Value) FROM YourTable T2
WHERE T2.Sort1 <= T1.Sort1) AS RunningSum
FROM YourTable T1
ORDER BY Sort1

T1 and T2 are aliasses for two independent references to
the same
table.

If you need a more complex running sum I could help with
it here.
Please post a query of your situation, omitting the
running sum. It
must include an ORDER BY clause putting the rows in the
sequence you
want them summed. If you want the sum to restart based
on grouping,
please state the columns on which it is to be grouped.
These columns
also should be the first columns of the ORDER BY clause.

From that I can likely create the running sum subquery
necessary.

One note on the ordering. If it is not unique, the
subquery will add
the new values for all the rows where the ordering is
identical
simultaneously. The query will present these rows
Click to expand...
one
at
a time, but
the running sum will not be able to distinguish between
them. If you
add one or more additional key fields till the ordering
is unique this
can be eliminated. When the set of ordering fields is
not unique, the
ordering among rows having duplicate ordering is
arbitrary, causing
this potential problem.

In large databases this can be a bit slow in some cases.

On Sun, 28 Sep 2003 07:44:13 -0700, "Bill"
<[email protected]>
wrote:

Is ther any way to do a running sum in a query? I can,
of
course, do this in a report, but I would like to do this
as part of an inventory check in a table or query.

Thanks to anyone who solves this for me,
Bill

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.


Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
.
Click to expand...
 
Dear Bill:

First, please try it without grouping and see if that is working.

Second, what effect do you want to implement by grouping by item?
What is it you want to change?

Tom,

I want to show each row but I only need to group by 1 item-
-ItemProductCode

Bill
Click to expand...

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

At this point I need to get something finalized, so I've
taken a report with running sums, dumped it to excel, and
then resorted the data. It works, but not very efficient
or something that can be documented for others to work.

Thanks for your help. I'll let you know if I ever solve
this. I don't want to waste anymore of your time.

Thanks again,
Bill
 
Back
Top