If I have an Invoice_Detail table, I'd like to have a running total of the items purchased.

I can execute the query:

 select Invoice_Id, Line_Num, Detail_Total, RunSum(Detail_Total) group by Line_Num;

and that gives me the results I want, but the "Group By" turns the query into case-insensitive (readonly). I don't really need a Group By since each Line_Num is unique.

So why can't RunSum() work without using a Group By clause?
I'd like to be able to execute:

 select Invoice_Id, Line_Num, Detail_Total, RunSum(Detail_Total) order by Line_Num;

and have the query case-sensitive (updateable).

TIA
Barry

Barry wrote:

<<So why can't RunSum() work without using a Group By clause? I'd like to be able to execute:

 select Invoice_Id, Line_Num, Detail_Total, RunSum(Detail_Total) order by Line_Num;

and have the query case-sensitive (updateable).>>

The aggregate functions (SUM, RUNSUM, COUNT, AVG . . ) all work without the GROUP BY Clause.  Without the GROUP BY Clause, the query will treat the entire table as a single group which is not what you want.

SELECT Invoice_Id, Line_Num, Detail_Total,
  (SELECT Sum(Detail_Total)
     FROM Invoices AS I1
     WHERE I2.Invoice_Id = I1.Invoice_Id AND
     I2.Line_Num >= I1.Line_Num) AS SumDetail
  FROM Invoices AS I2

Something like this (untested) gives a running sum with a sensitive query.

Richard Harding

Richard,
  That worked great, thanks.

Barry