Best Way to Present Recurring Records/Events

Topic

New Reply

What’s the best way to present recurring events/records? These recurring events/records consist primarily calculated fields.

Two examples:
1. Monthly bills (electricity, water, gas, credit cards,…etc.) occur repeatedly on certain day of a month.
2. Monthly home mortgage payments.
Should these events/records be created programmatically in a table/query? Or just merely display in the forms/reports?
What are the usual practice?

Armstrong

Reply | Quote

Replies

If you’re going to perform lots of calculations, Excel might be a better application for that kind of thing than Access.

In Access, you should store repeating data in a separate table, with one record per “event”, and present them in a continuous subform.
If the number of events is variable, you can add them as they occur. So the subform will only contain and display existing events/
If you know that there’s going to be a fixed number of events, for example one for each month of the year, you can make it easier for the user by creating a series of records in advance (using VBA code). The subform will display all events (for a year, for example) even if they haven’t occurred yet.

You can perform the calculations in a query, or in expressions in the control source of text boxes on a form (or report). This will ensure that the result of the calculations is always up to date.
However, if the number of records is very large and/or the calculations are very complex, this might become too slow. You could then use an update query or VBA code to store the calculated values in the table(s) and update the date periodically. Disadvantage is that the data aren’t guaranteed to be up to date.
You will have to decide based on your situation.

Reply | Quote

Hi HansV,
Thanks for your comment. I’ll study your solutions.
Definitely I’ll opt for Access. With Access, I can change priority, sorting and filter instantly with a click of command button. Excel can never match the power and convenience offered by the Access. Of course, Access takes more time to learn.
Thank you for sharing your design experience with me.
Armstrong

Reply | Quote