Monthly Fees Collection from Students

Question

Hi

can anyone help me to accomplish my database?

I have been working on a database "Students Fees Management" in MS ACCESS 2016. I want to know that if a student has not paid monthly fee for the last month for example October and in the current month November he pays fee half of 3000 and remains 1500. so i want to print its current month receipt only. print report fields are Arrears and Monthly Fee, Paid Fee and Balance.

Answer 1

Let's say you have table Transactions with columns TransactionID (autonumber primary key), TransactionDate, StudentID, FeeDue, FeePaid a query to return the cumulative balances and arrears would be like this:

SELECT T1.StudentID, T1.TransactionDate, T1.FeeDue, T1.FeePaid,
SUM(T2.FeeDue-T2.FeePaid) – (T1.Feedue -T1.FeePaid) AS Arrears,
SUM(T2.FeeDue-T2.FeePaid) AS Balance
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate)
AND (T2.StudentID = T1.StudentID)
GROUP BY T1.TransactionDate, T1.FeeDue,T1.FeePaid, T1.TransactionID, T1.StudentID
ORDER BY  T1.StudentID, T1.TransactionDate,T1.TransactionID;

---

Ken Sheridan, Stafford, England

Answer 2

You could look at my School Payments System application. Its commercial software but there is a free DEMO version you can try out in case it meets your needs.