Calculate Balance

S

Steven

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
 
M

Michel Walsh

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
 
S

Steven

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.
 
S

Steven

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?
 
M

Michel Walsh

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
 
M

Michel Walsh

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
 
Top