Calculating Amounted owed.

[Guest]

I have a club that I have setup a database for which has 3 tables. One is
membership info such as name address etc. One is dues and one is payments.
I need to keep track of each years dues and payments and be able to produce
a dues notice for each person that would state what they owed and if they are
in arrears. Would like to list each year in arrears and a total. Can
someone help me with this?

Keith

 

[Michel Walsh]

Hi,


Make a first query :

SELECT memberID, dateTime, - amountDue As Amount FROM dues
UNION ALL
SELECT memberID, dateTime, amountPaid FROM payments

Note the minus sign in front of the amountDue, so a simple SUM can be used :
amountPaid + (- amountDue)
Next, make a second query that will do that sum:


SELECT memberID, Year(dateTime), SUM(amount)
FROM previousQuery1
GROUP BY memberID, Year(dateTime)
ORDER BY memberID, Year(dateTime)



Hoping it may help,
Vanderghast, Access MVP