Results 1 to 3 of 3
  1. #1
    enginerdUNH is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Location
    United States
    Posts
    35

    Prevent previously entered record changes when updating calculation

    Hi,

    I am creating a budget database and i have a form where the user enters in their income and then after the user enters the income amount (i.e. after update) the income/expense categories (i.e. savings, spending, loans, debt, rent, extra) auto update with their pre-determined amounts or percentages of the total income amount. For example, the user enters in an income of $1500 and the income/expense categories are set as follows:

    savings: $25
    spending: 10%
    loans: $375
    debt: $50
    rent: $500
    extra: $400

    This data is then stored in the table and the user can continue entering in income and expenses. I have the database set so the user can also overwrite the calculated fields by inputting the value they want to allocate as long as the totals of all fields does not exceed the total income amount when they attempt to save the record. Now what i need to figure out is how to prevent the database from changing previously calculated/overwritten records if the user makes a change to the income allocations. For example, let's say user enters another $1500 in income and the allocations are now as follows:

    savings: $75
    spending: 25%


    loans: $400
    debt: $50
    rent: $500
    extra: $100

    The allocations for this new record are of course different but the previously entered records need to remain as they were previously entered. What i am experiencing is that any previously entered records are not being "exempted" so to speak by the changing of the income allocations and are being incorrectly updated any time the income allocations are modified in any way. I am in need of some troubleshooting help to figure out how to prevent this from occurring. Any help you can give is appreciated.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There has to be at least one field common between the 2 'groupings' that can hold a value that ties groups of records together, but that field must hold different values. For example, if you're wanting to have a line item approach (like a sales order) where 1 entity can perform aggregate functions over a group (like recording multiple sources of income and summing them) the incomes must pertain to the same person and all the required fields have values, but they are differentiated from one another by IncomeID (for example). If you don't have this sort of setup, then I have to wonder if your db is properly normalized as a whole.

    Not 100% certain, but your case could be one of the few where storing calculations is required. If not, it's because all income sources and all expenses should be separate records and the form performs the calculations based on the sums of your fields. That is to say, sum of incomes minus sums of expenses = remaining balance, in which case the amounts should not be calculated then stored at all. Also, if you have expenses and/or incomes going left to right in the fields of a table (like a spreadsheet) then your db is definitely not normalized. Continuing on with it will likely cause you more grief as you go.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to micron's comments it would be helpful if you showed us your table(s) design.
    It sounds like you need to specifically deal with Transactions and TransactionTypes.
    You can calculate -balance and various groupings via queries or code.
    Good luck.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-19-2017, 04:32 PM
  2. Additional Value List edits erasing previously entered fields
    By rshepard@shepardsearch in forum SharePoint
    Replies: 2
    Last Post: 06-27-2017, 07:19 AM
  3. How to recall information previously entered
    By Accu-Grind in forum Forms
    Replies: 4
    Last Post: 11-07-2015, 06:34 AM
  4. Replies: 8
    Last Post: 02-09-2012, 02:02 PM
  5. Replies: 3
    Last Post: 05-26-2011, 12:52 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums