Your best bet is to go out and get one of the many books that covers
standard SQL.

select name, sum(amount) as total from clients, accounts
where clients.client = accounts.client
group by clients.name

-Mike

Yes I have a manual and still to no avail after reading it.

I have a table called Clients and it contains an index called ClientID
I have a table called Accounts and it contains an index called ClientID and
Amount

I want to run a select statement that will yield all of the clients in
surname order and add (SUM) all of the Amounts by ClientID

eg

something like

select *
from Clients C, Accounts A
where C.ClientID = A.ClientID
order by C.Surname

Any genius's know how and where to put the sum command in the above so i can
get the total amounts each client owes.

I will obviously later create a parameter to sort them by groups of dates to
perform a trial balance by current, 30 days, 60 days, 90 days etc.

A definative answer will be most appreciated

David Morris

If you've got ACCESS, compose a query with access, then just click View/SQL.
eg
SELECT Subcontract.Job, Sum(Subcontract.Amt) AS SumOfAmt,
Avg(Subcontract.WhenPaid) AS AvgOfWhenPaid
FROM Subcontract
GROUP BY Subcontract.Job;

On Fri, 6 Aug 1999 12:35:52 +1000, "David Morris"

[...]

The SUM function will go in the SELECT clause. But you will also need to
add a GROUP BY clause based on your SurName column to force the SUM
function to produce one total (subtotal, actually) for each customer.

  SELECT C.ClientID, SUM(A.Amounts)
  FROM Clients C, Accounts A
  WHERE (C.ClientID = A.ClientID)
  GROUP BY C.ClientID
  ORDER BY C.ClientID

Assuming the date field is in the Accounts table (for example purposes
we'll call it DueDate) and the date for calculating dates due is today
(08/06/1999):

  SELECT "30 days" AS Period, C.ClientID, SUM(A.Amounts) AS Amounts
  FROM Clients C, Accounts A
  WHERE (C.ClientID = A.ClientID) AND
    ("08/06/1999" - A.DueDate BETWEEN 0 AND 30)
  GROUP BY C.ClientID
  UNION ALL
  SELECT "60 days", C.ClientID, SUM(A.Amounts)
  FROM Clients C, Accounts A
  WHERE (C.ClientID = A.ClientID) AND
    ("08/06/1999" - A.DueDate BETWEEN 31 AND 60)
  GROUP BY C.ClientID
  SELECT "90 days", C.ClientID, SUM(A.Amounts)
  FROM Clients C, Accounts A
  WHERE (C.ClientID = A.ClientID) AND
    ("08/06/1999" - A.DueDate BETWEEN 61 AND 90)
  GROUP BY C.ClientID
  ORDER BY 1, 2

Here are a few SQL books I like and use.

  Understanding the New SQL, a Complete Guide
  Jim Melton & Alan R. Smith
  Morgan Kaufmann, 1993
  ISBN: 1-55860-245-3

  SQL Instant Reference
  Martin Gruber
  Sybex, 1993
  ISBN: 0-7821-1148-3

  A Guide To the SQL Standard, Third Edition
  C.J. Date & Hugh Darwen
  Addison-Wesley, 1993
  ISBN: 0-201-55822-X

  The Practical SQL Handbook, Second Edition
  Judith S. Bowman, Sandra L. Emerson, & Marcy Darnovsky
  Addison-Wesley, 1993
  ISBN: 0-201-62623-3

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

Get "SQL for Dummies".  It's a great book to get started.  You will even finding
yourself using it for reference.  It covers the SQL-92 standard nicely.

--
Spam Guard:
All replies sent to this address from hotmail.com will be deleted.

If you don't have access, a fellow wrote one in Delphi called Duck Query
Editor.  It's pretty good, and it's freeware.  Go to www.ducktech.com.

--
Spam Guard:
All replies sent to this address from hotmail.com will be deleted.