• Registered: 2016-01-17
  • Posts: 170

Topic: Automatic Monthly Membership Fee Registration

Hello,

I am developing a student management system, and I need some guidance about how to automate the monthly membership fee registration process.

In this software, I have a large number of student records. I need to register their monthly membership fees automatically instead of entering each record manually.

The annual membership fee is fixed for each student and is already stored in the Student table. However, I need to create monthly payment records in a TableGrid (for example, January, February, March, etc.).

My idea is to have two ComboBoxes for selecting the year and month. Then, by clicking a button, the software should automatically create a monthly membership fee record for all students based on the selected year and month.

For example, if I select:
Year: 2025
Month: march

and click the button, the software should automatically add a payment record for all students in the payment table, using the membership fee amount stored in the Student table.

Could you please guide me on the best way to implement this in My Visual Database?

Thank you.

I have attached a sample

Post's attachments

Attachment icon Bank.rar 318.71 kb, 40 downloads since 2026-08-01 

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,449

Re: Automatic Monthly Membership Fee Registration

Hi,

1. For example, in the script:

INSERT INTO bank (id_student, monthlyfee, id_year, id_month)
select id, fee, 2, 1 FROM student

Where 2 is the ID from the year table. 1 is the ID from the month table. (The values are substituted from the form).
2. Can a new student enroll while others are already studying? You'll need to rewrite the query above.
3. Why create a separate form for such an operation if, for example, when the program is launched for the first time in a new month, new records can be generated automatically. Check the Bank table for new records. Year, month. If not created, create them.
4. Is it possible to make partial payments?
5. Can the monthly payment amount change during the course of study?
6. In which column and table will you track payments?
7. How will you display the reporting in a table or report? This is a fascinating topic with pivot tables.

  • Registered: 2016-01-17
  • Posts: 170

Re: Automatic Monthly Membership Fee Registration

Hi sparrow

Thank you for your previous reply.

My situation is a little different, so I would like to explain it in more detail.

* My **Student** list is fixed. The number of members does not change during the year.
* Each student has a fixed monthly membership fee stored in the **Student** table (field: `fee`).
* The membership fee does **not** change during the year.
* Members cannot pay less than the required amount. At the beginning of every month, the exact same amount must be recorded for every student.

I created a form (auto) with:

* ComboBox3= Year
* ComboBox1 = Month
* Button1 = Register monthly fees

When I click the button, I want one SQL command to automatically insert a record for **every student** into the `Bank` table, using:

* `id_student` = Student ID
* `monthlyfee` = Student.fee
* `id_year` = Selected year
* `id_month` = Selected month

You suggested using:

```sql
INSERT INTO bank (id_student, monthlyfee, id_year, id_month)
SELECT id, fee, 2, 1
FROM student;
```

Should I execute this SQL in the **Button → SQL Query** action, or should it be executed from **Pascal Script** (for example using `SQLExecute`)?

Also, I have attached a **simplified sample** of my project. If necessary, I can attach the complete project as well.

Thank you for your help.

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 3,070

Re: Automatic Monthly Membership Fee Registration

Hi Identify, Sparrow,
Attached is an old project that I've quickly changed to do the sort of thing I think you're after.
A couple of things to mention;
1.  I'm not sure what benefit you get from having 'year' and 'month' tablles - can't you just use a normal 'date' field formatted to show month/year?
2.  If the fee is held on the 'student' table and is always the same, there's no real need to also hold it on the 'bank' (or 'payments' in my example) table.
I've not done any testing on it and there's not much by way of validation but you could always add that in later.
As Sparrow mentioned before, I'm surprised that your number of students is fixed (you don't get any late starters or students that leave the course?) or have a requirement for part-payments etc.  You're lucky if that's the case as this is usually where the complexity lies.
Regards,
Derek.

Post's attachments

Attachment icon Bank suggestion.zip 984.96 kb, 54 downloads since 2026-08-04 

5 Reply by sparrow (edited by sparrow 2026-08-02 17:55:08)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 1,449

Re: Automatic Monthly Membership Fee Registration

Hi Derek, Identity.


Identity ,
Where you use this query doesn't really matter. The main thing is to pass the ID (year and month) from the form.
Your save button will lose its effect and will either become a SQL Query or perform an action from a script when clicked. Your choice.
The questions I wrote are the ones I would have if I were writing something similar. It's good that you know the answers to many of them.
If your conditions are truly so lenient, then, as Derek correctly noted, you don't need to copy the amount from the student table to the bank table. You can always display the payment from the student table in the reporting table.
Derek is also correct about the year and month tables.
You can directly enter the year and month into the bank table. I would recommend automating this process, as I already wrote, when starting the program in the last days of the previous month or the first days of the month when payment is due.
But your solution is also valid. Pressing a button is a good job.
The question about the number of students remains unasked and unanswered. My solution will be faster with a large number of students. Derek's solution will need to be adjusted using a transaction for recording. Both solutions will allow you to set filters, if necessary, based on various student parameters.