locked
Monthly Fees Collection from Students RRS feed

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


    Saturday, November 23, 2019 6:28 PM

All replies

  • 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

    Saturday, November 23, 2019 7:13 PM
  • 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.

    Sunday, November 24, 2019 4:34 PM