Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •