Having trouble with a query

I am having a ^$#%# of a time with a query for a project.

The table that I have looks like this:

Lname Fname SSN Adult_SSN Balance_due

-------- -------- -----------  ----------- ------------
Cass      Gary    222-33-4444                       4
Cass      Donald  333-44-5555  222-33-4444          0
Archer    Peter   828-22-9876                       0 
Archer    Sue     987-66-5544  828-22-9876          2.5
Cass      Mark    777-88-9966  222-33-4444          1
Borland   Kevin   555-22-1114                       1.5
Borland   Jean    555-22-4441  555-22-1114          0

The entries with the adult_ssn's are children that the parent (the entry with the same ssn as the adult_ssn) are responsible for. I need to show the lname, fname, and sum of balances due for the parent, with the balances due of their kids included. I have been working on this one for many hours, and either am having a problem where it is counting the balance of Cass double, or since I have to show the fname also, grouping it by lname, fname. I know that I should use a count somewhere in the join to combine the kids balance_due, but haven't been able to figure this one out.

the final outcome should look like this

Lname    Fname          sum(balance_due)
------   ------         ---------------
Archer   Peter                      2.5
Borland  Kevin                      1.5
Cass     Gary                         5

Does anyone out there know how to solve this one? Received on Mon Dec 07 1998 - 03:38:37 CST