Please Help with Query to Return a Balance Column

[LuvToGlass]

Sorry for the 2nd posting, I could use some help with this. I have a
lot to learn about queries and hope that someone's experience would
guide me. I need a column providing a running balance taking in
account the following criteria:
INV (on hand) - O_QTY (Order Qty) + S_QTY (Supply Qty) for each
SKU#.
Returning a column something like the Balance column below:

Balance
-7
25
10
2
-6
0
-23
1
-7
2
-13
2
-6
0

Sample Data:
SKU# C_ORDER# D_DATE O_QTY INV S_QTY S_DATE
9936618 CD-80411211/1 08/22/2007 -7 0
9936618 32 08/24/2007
9936618 CD-80411211/2 08/29/2007 -15
9936618 CD-80411211/3 09/05/2007 -8
9936618 CD-80411211/4 09/24/2007 -8
9936618 6 09/24/2007
9936618 CD-80411211/5 10/01/2007 -23
9936618 24 10/01/2007
9936618 CD-80411211/6 10/15/2007 -8
9936618 9 10/15/2007
9936618 CD-80411211/7 10/22/2007 -15
9936618 15 10/22/2007
9936618 CD-80411211/8 10/29/2007 -8
9936618 6 10/29/2007
Thank you

 

[Ken Snell \(MVP\)]

Will you be using the query to provide data to a report? If yes, it's much
easier to use the RunningSum property of a textbox on the report to do
this -- a query will require the use of a subquery that may greatly slow
down the speed of the query when it runs.

 

[LuvToGlass]

Thanks for your time, I'll be taking the query results as an export to
a delimited file.

 

[Ken Snell \(MVP\)]

In that case, the query would look something like this:

SELECT T.*,
(SELECT Sum(A.INV - A.O_QTY A.S_QTY)
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;

 

[Ken Snell \(MVP\)]

Sorry - typo -

SELECT T.*,
(SELECT Sum(A.INV - A.O_QTY + A.S_QTY)
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;
--

Ken Snell
<MS ACCESS MVP>

In that case, the query would look something like this:

SELECT T.*,
(SELECT Sum(A.INV - A.O_QTY A.S_QTY)
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

Thanks for your time, I'll be taking the query results as an export to
a delimited file.

 

[Ken Snell \(MVP\)]

And, in case there might be a Null value in any of the three fields related
to inventory amounts:

SELECT T.*,
(SELECT Sum(Nz(A.INV,0) - Nz(A.O_QTY,0) + Nz(A.S_QTY,0))
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;
--

Ken Snell
<MS ACCESS MVP>

Sorry - typo -

SELECT T.*,
(SELECT Sum(A.INV - A.O_QTY + A.S_QTY)
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;
--

Ken Snell
<MS ACCESS MVP>

In that case, the query would look something like this:

SELECT T.*,
(SELECT Sum(A.INV - A.O_QTY A.S_QTY)
FROM TableName AS A
WHERE A.D_DATE <= T.D_DATE AND
A.[C_ORDER#] <= T.[C_ORDER#] AND
A.[SKU#] = T.[SKU#]) AS SKU_Balance
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

Thanks for your time, I'll be taking the query results as an export to
a delimited file.