What you're wanting to do is possible, but not too easy. This is due to the
way that you have set up the table.
If you could change the table structure so that the curAmount field
contained a positive number for the charges and a negative number for the
credits then the query would be very simple, and there would be no need for
the ysnCredit field.
If for some reason you cannot change the table structure, then you will need
quite a complex query, or possibly more than one query combined to give the
result you want. You can certainly write a query that produces the desired
results by using an totals query containing two subqueries - one to
calculate the charges ane one the credits for each client - plus a
calculated field to work out the difference between the two. Oh, and since
the subqueries could return null values you would need to use the Nz
function on the subqueries so the nulls are converted to zeros.
I am assuming that you want the query to return a single row for each client
that shows the total of charges minus credits for that client. The SQL for
your query would look something like this:
SELECT tblName.ClientID, Nz((SELECT Sum(curAmount) AS x FROM tblName AS a
WHERE a.ClientID = tblName.ClientID AND a.ysnCredit = True
,0) AS Credits,
Nz((SELECT Sum(Amount) AS x FROM tblName AS a WHERE a.ClientID =
tblName.ClientID AND a.Credit = False
,0) AS Charges, [Charges]-[Credits]
AS Totals
FROM tblName
GROUP BY tblName.ClientID
ORDER BY tblName.ClientID;
(You need to replace tblName with the name of your table and ClientID with
the name of the field containing the client ID).
