Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Scary Expense Calculations

    I'm creating a database where a number of people (debtors) have expenses and make payments for development of some land. Each person "owns" a certain proportion of the land ie 1.2 hectares, 3.5 hectares and so on and this at the moment is also reflected as a % of the whole piece of land. There are about 50 people involved. Some expenses will get entered as transactions which have to be charged to a single debtor and then GST plus interest is calculated on that amount. However there are occasions where a single expense must be charged to all 50 of the debtors according to their % proportion of the land. In the past a nightmare spreadsheet has been used with an if statement that goes on forever but is basically saying "if the debtor number cell is blank, then charge this expense to everybody according to their proportion".

    Each debtor has a 5 digit numeric ID. I would anticipate not having a blank in the database but maybe 11111 being the ID to select when the expense is shared. I guess I still need an "if" statement type of thing that distributes the expense to all debtors if the ID is 11111.

    My problem. I can't get my head around how to set up the table so the client can enter a single debtor expense or one of these multiple ones. Maybe it can't be done in the one table?? Ultimately the data has to be extracted to produce a statement for each debtor every three months which lists all payments and expenses (proportionate and otherwise) plus interest plus GST for that debtor.

    Should I have one table (and therefore form)for single debtors and another for the multiple one in which case the client would choose the appropriate form and then enter the expense transaction?

    Any ideas out there? My brain is hurting!

  2. #2
    art013
    Guest

    Re: Scary Expense Calculations

    Suzanna, you can do it either way. I would likely use just one table with a status flag in it to note if it is a specific or an proportional charge. (You could use the 11111 method as well; but then you would need a corresponding "proportional" entry in your "Debtor" table, right?[img]/w3timages/icons/smile.gif[/img])

    In any case, when you produce your statements, your calculation of the amount is based on the all/prop flag: If it is all post the charge on the statement, else multiply the charge by the % of the property owned. There might be a rounding issue depending on the numbers involved, so if you need to be accurate to the penny, you might need to do some rounding.

    Hope this helps.

  3. #3
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scary Expense Calculations

    Never thought of the flag idea. Thanks for that Art.

    However I'm still wondering even by choosing the "All" flag, how the proportionate amounts would actually be put against the other 49 debtors without doing 49 transaction entries. In the spreadsheet the "If" statement just dumped the proportionate amounts into a cell in each debtor's part of the worksheet, but I can't see how that would happen here because it would need a expense transaction against each debtor.

    Should I be thinking along the lines of creating some sort of clever update query to whiz through assigning the % to each of the 50 debtors?

  4. #4
    art013
    Guest

    Re: Scary Expense Calculations

    It sounds like you need an entry for each charge, my mistake. I would still use the flag idea for the "transactions" and then "post" the transactions by running the update query on the proportionate items distributing them to each debtor. This allows you the chance to change the proportinate charge (not that it would ever need to be changed) up to the time you "post" it without having to modify or back out the series of transactions.

    This gets more to an accounting type system, but is still fairly straight forward. Let me know if you need more info or an example.

  5. #5
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Scary Expense Calculations

    An example would be thrilling [img]/w3timages/icons/clever.gif[/img]

Posting Permissions

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