1. ## 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. ## 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. ## 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. ## 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. ## 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
•