Can you help me run the code to generate challans for active students,

[moin555]

Can you help me run the code to generate challans for active students, and if any student pays less than the required amount, the remaining balance should be added to the next month's challan? File attach . ist of 1 month code is run

 

[moin555]

Can you help me run the code to generate challans for active students, and if any student pays less than the required amount, the remaining balance should be added to the next month's challan? File attach . ist of 1 month code is run

On the 1st of every month, the code should run automatically to update the challan form for all active students.

 

[NauticalGent]

Is a "Challan" some sort of financial statement?

 

[moin555]

Is a "Challan" some sort of financial statement?

monthy fee slip

 

[Pat Hartman]

You need to use a union query. One part selects the current fees and the other selects the late amounts. The two together make up the current invoice.

Running something ON the 1st is probably not the best idea since sometimes the 1st is not a work day. If you want this to run when someone pushes a button, then you can just use the year/month as the selection criteria. If you want something to run automagically, then I tend to create a log table and check the current date against the log table and run the query on the first business day of the month, whatever that is. Then add a log entry to record when the process runs so that it will only run once for each month.

 

[moin555]

You need to use a union query. One part selects the current fees and the other selects the late amounts. The two together make up the current invoice.

Running something ON the 1st is probably not the best idea since sometimes the 1st is not a work day. If you want this to run when someone pushes a button, then you can just use the year/month as the selection criteria. If you want something to run automagically, then I tend to create a log table and check the current date against the log table and run the query on the first business day of the month, whatever that is. Then add a log entry to record when the process runs so that it will only run once for each month.

yes only once for each month

 

[DickyP]

monthy fee slip

Glad someone asked this before me. Discovered (eventually) that it is a hindi word, having found no trace of it in either theOED or Webster. I've learned something!

 

[Ken Sheridan]

You might find the following query which records the monthly payment of membership fees of interest:

SELECT
    T1.TransactionID,
    T1.MemberID,
    Members.FirstName & " " & Members.LastName AS FullName,
    T1.TransactionDate,
    T1.FeeDue,
    SUM(T2.FeeDue - T2.FeePaid) - (T1.Feedue - T1.FeePaid) AS Arrears,
    T1.FeePaid,
    SUM(T2.FeeDue - T2.FeePaid) AS BalanceDue,
    (
        SELECT
            COUNT(*)
        FROM
            Members AS M1
            INNER JOIN (
                SELECT DISTINCT
                    MemberID
                FROM
                    Transactions
            ) AS T2 ON T2.MemberID = M1.MemberID
        WHERE
            M1.LastName & M1.FirstName <= Members.LastName & Members.FirstName
    ) AS MemberIndex
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.MemberID = T1.MemberID)
    )
    INNER JOIN Members ON Members.MemberID = T1.MemberID
GROUP BY
    T1.TransactionDate,
    T1.FeeDue,
    T1.FeePaid,
    T1.TransactionID,
    T1.MemberID,
    Members.LastName,
    Members.FirstName,
    Members.FirstName & " " & Members.LastName
ORDER BY
    Members.LastName,
    Members.FirstName,
    T1.MemberID,
    T1.TransactionDate,
    T1.TransactionID;

The model for the database is:

The query is the RecordSource for a subform in the following form:

As you can see any outstanding fee due after each transaction is carried forward as arrears to the next transaction. The computed MemberIndex column returned by the query is merely to allow for the differentiation of each member for the purposes of conditional formatting to show alternating back colours per member.

 

[moin555]

Monthly Fee Generation Process when i click on button all student deatils update in subfrom and print all challan in one click