Calculate Balance

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am designing a course managment system and need to calculate the tution
balances. I have 3 tables Enrollment, Enrollment Details & Payments. An
enrollment can have many Enrollment Details, each with a price. I would like
to calculate the Enrollment totals (sum of the prices in enrollment details
per enrollment - thats easy) minus any payments made. Do i do this all in the
query or on the form? Whats the best practice? How would i do this in either
scenario? Thanks
 
Hi,


SELECT a.accountID, Nz(SUM(b.amountDues),0) - Nz(SUM(c.amountPaid),0) As
balance
FROM (a LEFT JOIN b ON a.accountID=b.accountID)
LEFT JOIN c ON a.accountID = c.accountID
GROUP BY a.accountID


where a is the table of all accounts, b is the table with details of what
is due, by accountID (can have many details for one account), and c is the
table of payments made, if any, again, can have none or multiple details
(multiple payments), for each account.



Hoping it may help,
Vanderghast, Access MVP
 
Thanks but Nz() is not working, I get an ADO error: 'Nz' is not a recognized
function name. i looked up its purpose and think it will be helpfull, but its
not working in this query.
 
Also, there are different types of payments that need to be deducted or added
based on their type (credits, Discounts and payments), should i create
separate queries for them and aggregate them in this balance query? or can i
have sub criteria for each of the additions or subtractions?
 
Hi,


If Nz() does not work, that is probably because you are outside Access
itself.

Nz(a, b)

can be replace by

iif( a IS NULL, b, a)


in Jet-SQL. If you use MS SQL Server, use

COALESCE( a, b)



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


I am not sure I understand, but maybe you can use more fields in the GROUP
BY clause to define different ... groups. In a Total query, the data is
considered just once (you cannot have and sub-total and total), unless you
specifically duplicate the data through a join... or make multiple queries
(one for the sub-total, one for the total). In MS SQL Server, you can use a
ROLLUP (or a CUBE) to get subtotal and total, in one query.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top