I've got the following tables:
Master : Code,Discount
Detail   : Code,Item,Amount

These two are set up as master/detail so as I step through Master, the
detail grid get updated.
Now I need to have a 'Totals' Query with fields :Code,Sum(Amount)
where as I step through Master, that a grid displaying Totals also get
updated automatically. Sum(Amount) must just show the sum of all detail for
the selected (in master) CODE (i suppose a 'group by' would surface
somewhere).

SUMMARY:
as i step through Master, Details and Totals must be updated (refreshed)
where Detail have more that one row per master row and totals have one row
for every master row.

Hope I've explained well enough.

Thanks in advance
Anton Ekermans

If you have a second query with its data source set to your master table this
query can sum your detail records.  You will have to fire this query in the
detail table afterPost and AfterDelete methods to keep it up to date.

Alternately if you have the client server/Enterprise version of Delphi 4 or 5
look at using a TclientdataSource which supports aggregate fields.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

By 'fire' do you mean to Close and re-Open?
Brian Bushay TeamB <BBus...@Nmpls.com> wrote in message
news:37db0ad2.54358090@floyd...

On Thu, 2 Sep 1999 03:52:15 +0200, "Anton Ekermans" <ek...@inetcom.co.za>
wrote:

You can do the aggregation of the Detail in a TQuery. Use the SQL function
SUM on the Amount column. Add a GROUP BY clause to this SELECT statement
and base it on the Code column. This causes the operation of the SUM
function to be performed once for each distinct value in Code (a subtotal
for each item).

  SELECT Code, SUM(Amount)
  FROM Detail
  GROUP BY Code

As for updating the Master table, you can do this in an SQL statement. The
UPDATE statement can be used to apply values to the Master table. Use as
the the source for UPDATE a correlated SELECT subquery, one similar to the
preceding statement.

  UPDATE Master
  SET Total =
    (SELECT SUM(Amount)
    FROM Detail
    WHERE (Detail.Code = Master.Code))

This obviates the need to aggregate with a TQuery and then use Delphi code
to traverse the rows of the target table to apply the aggregated values.
Here both aspects are handled in one fell swoop.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Thanks Steve.
But if I scroll in the master table, the detail aggregate are not updated.
I'm using TQuery for both grids(master/detail).

To elaborate on the query...
Say I want to have a calculated value based on an aggregate in the detail
and a value in the master:
(If this in right...)
  SELECT Code, SUM(Amount) SubTotal,(Sum(Amount)*Master.Factor) Total
  FROM Detail,Master
  GROUP BY Code

How will the SQL parser know if I mean Detail.Code or Master.Code in
select/group by...?
Can I use values from the master table like this?
Thank you Steven

On Thu, 2 Sep 1999 21:02:43 +0200, "Anton Ekermans" <ek...@inetcom.co.za>
wrote:

In this case, it is not going to matter whether it uses the Code field from
the Master or the Detail table. If the Detail table has ten records with a
given Code value, the Amount field would still be totalled to produce one
aggregate record for that Code value. But if you really need to specify
which table a field comes from, use a table correlation name (explained in
the local SQL help). In the statement below, "M" and "D" are table
correlation names.

  SELECT M.Code, SUM(D.Amount) SubTotal,
    (SUM(D.Amount) * M.Factor) Total
  FROM Detail D, Master M
  WHERE (D.Code = M.Code)
  GROUP BY M.Code

Maybe you need a TQuery Master-Detail link. The Detail TQuery component's
SQL would look like that below:

  SELECT M.Code, SUM(D.Amount) SubTotal,
    (SUM(D.Amount) * M.Factor) Total
  FROM Detail D, Master M
  WHERE (D.Code = M.Code) AND (M.Code = :Code)
  GROUP BY M.Code

Then, set the DataSource property of the Detail TQuery component to the
TDataSource for the Master TQuery. This will cause the Detail TQuery
component to only produce one total, for the records with the same Code
value as in the current record of the Master TQuery. Scroll the Master
TQuery and the Detail TQuery is automatically requeried to produce a new
total for the new Code value.

==========================================================================
Steve Koterski                  "Computers are useless. They can only give
Technical Publications          you answers."
Borland                                       -- Pablo Picasso (1881-1973)
http://www.borland.com/techpubs/delphi

Thanks a lot Steve !!!
Your help is truly appreciated.
Anton |:'>=

Yes

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com