I have a table that has these fields

MyDate
MyAmountPositive
MyAmountNegative
MyTotal

How can I make an UPDATE statement that will put the correct amount in MyTotal based on the value in MyAmountPositive or MyAmountNegative and in MyDate order?

Here is how MyTotal would be calculated based on the values below:

MyAmountPositive, MyAmountNegative, MyTotal
100, 0, 0
0, 50, 50
500, 0, 550
0, 200, 350
100, 0, 450
0, 300, 150

I have done this with calculated fields in the past but I need the Total based on MyDate and I need to show the data in a different order.

Brent

I'm sorry, but I don't understand how are you calculating the value of MyTotal...

--
Fernando Dias
[Team Elevate]

Brent,

Ok, I understand it now
Here it is:

  SELECT
    MyDate, MyAmountPositive, MyAmountNegative, RUNSUM(MyAmountPositive - MyAmountNegative) AS MyTotal
  GROUP BY
    MyDate

--
Fernando Dias
[Team Elevate]

Fernando


Not the clearest request for assistance - I'd come to the same conclusion as you have (I was going to respond when I returned from shopping) BUT I'm still confused as to wether UPDATE or SELECT sql is wanted.

Roy Lambert

Roy,

It's either SELECT or Navigational Methods...
I can't figure out how to do it with an SQL UPDATE statement.

--
Fernando Dias
[Team Elevate]

Fernando

>It's either SELECT or Navigational Methods...
>I can't figure out how to do it with an SQL UPDATE statement.

I "think" I can using a script and an in-memory table (I have a vague memory of the great John Hay doing something similar), but until I know if all that's needed is a SELECT I don't really want to spend time trying

Roy Lambert [Team Elevate]

Here is the equivalent with a TDBISAMTable.  The SELECT statement posted works but I need it to modify the field values.  I thought that maybe using a query might be faster

procedure TForm1.Button1Click(Sender: TObject);
var
 MyTable : TDBISAMTable;
 cTotal : Currency;
begin
 cTotal := 0;
 MyTable := TDBISAMTable.Create(Self);
 with MyTable do try
   TableName := 'c:\temp\mytable.dat';
   IndexFieldNames := 'MyDate';
   Open;
   First;
   while not Eof do begin
     Edit;
     if FieldByName('MyAmountPositive').IsNull = False then begin
       cTotal := cTotal + FieldByName('MyAmountPositive').AsCurrency;
       FieldByName('MyTotal').AsCurrency := cTotal;
     end else begin
       cTotal := cTotal - FieldByName('MyAmountNegative').AsCurrency;
       FieldByName('MyTotal').AsCurrency := cTotal;
     end;
     CheckBrowseMode;
     Next;
   end;
 finally
   FreeAndNil(MyTable);
 end;
end;

Brent


One of the reasons that SQL is often faster than navigation methods is it doesn't bother with the UI. The way you're doing it, creating a table in the function, is essentially ignoring the UI as well so unless you have a lot of records to update you won't see much difference with SQL.

Now I have an idea what you're after, can I have a couple more bits of information before I mess around with a script:

1. How many records are you talking about? How long does it currently take?
2. Are there any duplicate dates or can there ever be? If so you've probably had it since RUNSUM needs a GROUP BY, and I can't think of any other way to do it in SQL.


Out of interest you show only integers in your example but you're using AsCurrency in your code. If you look at the source for TField you'll see that currency just translates to float so it may be a little more efficient to do something like

procedure TForm1.Button1Click(Sender: TObject);
var
 MyTable : TDBISAMTable;
 cTotal : extended;
begin
 cTotal := 0;
 MyTable := TDBISAMTable.Create(Self);
 with MyTable do try
   TableName := 'c:\temp\mytable.dat';
   IndexFieldNames := 'MyDate';
   Open;
   First;
   while not Eof do begin
     Edit;
       cTotal := cTotal + FieldByName('MyAmountPositive').AsFloat - FieldByName('MyAmountNegative').AsCurrency;
       FieldByName('MyTotal').AsFloat := cTotal;
     end;
     Post;
     Next;
   end;
 finally
   FreeAndNil(MyTable);
 end;


Roy Lambert [Team Elevate]

Roy,

<< One of the reasons that SQL is often faster than navigation methods is it
doesn't bother with the UI. The way you're doing it, creating a table in the
function, is essentially ignoring the UI as well so unless you have a lot of
records to update you won't see much difference with SQL. >>

Unless he's using C/S access with the DBISAM Database Server, in which case
the SQL will be much, much faster.

Tim Young
Elevate Software
www.elevatesoft.com

Tim


><< One of the reasons that SQL is often faster than navigation methods is it
>doesn't bother with the UI. The way you're doing it, creating a table in the
>function, is essentially ignoring the UI as well so unless you have a lot of
>records to update you won't see much difference with SQL. >>
>
>Unless he's using C/S access with the DBISAM Database Server, in which case
>the SQL will be much, much faster.

Is that just down to network traffic or something else?

Roy