Update multiple records in sub form (1 Viewer)

[pandy]

Hi all,

I created a school database which I use to communicate with parents via emails, text, etc via third-party apps. I am having a slight issue with the Fees sub form of this database. To add a new record of Fees, I have to add it to each student record individually. This is tiresome and I believe there must be an easier way

Ideally, I would like to pull up the list of senior secondary students and add the Fees to all students in that group who pay the same amount. For example, all senior secondary students in years 7-9 pay the same amount i.e. Junior secondary rates and all others in years 10-12 pay a different rate.

Can I update multiple student accounts with the same bill, rather than my current method of adding it to each student via the subform? Please let me know, thank you.

 

[theDBguy]

Hi. Welcome back! You can certainly automate some repetitive tasks in Access. For example, you can try to use an Update query to edit a bunch of records at the same time.

 

[LarryE]

Use an Update Action query. Create a query with all the students for whom you wish to update fees. Then create an update query from that query. Query Design>Update. Use the Update To criteria field to enter the fee amount and run the query.
Let's say you name the query UpdateFees. You can run the query with VBA code with the command:
DoCmd.OpenQuery "UpdateFees", acViewNormal, acReadOnly

 

[Pat Hartman]

Use an Update Action query. Create a query with all the students for whom you wish to update fees

This is the same as the inventory problem where instead of creating transactions to move inventory around, you update the count and obliterate any ability to audit what happened to make the count/fee change. (sorry to pick on Larry because theDBGuy said it first)

Alway use separate records for separate fees.

If you can identify a set of students to which you want to add new fees, then you would use an append query that selects the set of students and appends a new fee record for each of them.

@pandy I also don't see anything on your form that indicates what the fee is for. So, I'm guessing that your schema is incorrect and you really were looking for an "update" solution rather than the correct "append" solution.

 

[GaP42]

Further to the above, I would expect fees relate to a specifically identified Financial/Calendar Year AND to an Academic Year. Do you have a table which relates the general school fee to Academic Yr and the Calendar year in which it applies?
The application of an APPEND query is needed so as to not overwrite the records from earlier fee assignments (keep your history).
Typically the process is applied to create the records for a future date (or from the current date) for all currently enrolled students.
Once created, the process of sending bills etc is a next step.
New school enrolments and leavers need to be handled on the occasion of the action of enrolling or leaving the school (and the promotion of students from one academic year to the next - including exceptions, before the application of fee assignment)

 

[ebs17]

To edit several records at the same time, you will need to use an action query.
As Pat correctly states, an update query destroys historical data. Under German tax law, relevant data must be retained for at least 10 years. But the school itself will also have its own historical development, which will be very important and interesting.

So you have to preserve existing data and create new records for new periods and new fees, then with an append query.
If you need further help, you should show the database schema with the relationships between the tables. Images of forms are regularly meaningless.