Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2017
    Posts
    6

    How to arrange money into funds, when funds can share expenses

    If I am managing a club which has expenses (e.g. coffee, building repairs, electricity, cake, etc). Every time the club takes on an expense, then a record is added to the 'All Actual Expenses' table:



    Code:
     ______________________      _______________________
    |All Possible Expenses |    |All Actual Expenses    |
    -----------------------      -----------------------
    | ID                   |    | ID                    |
    | Name of expense      |    | AllPossibleExpensesID |
    -----------------------     | Date Occured          |
                                | Price                 |
                                -------------------------
    Expenses can be arranged into funds (like 'Expenses of year 2017', 'Food Expenses', 'Expenses of business meeting held to discuss premises' etc.).

    So the tables for funds is:
    Code:
     ___________      _________________________________________
    | Funds     |    | Funds LINK AllActualExpenses            |
    ------------|    |-----------------------------------------|
    | Id        |    | FundID                                  |
    | Fund Name |    | AllActualExpensesID that's in this fund |
    ------------      -----------------------------------------
    As you can see, funds can 'overlap', so an expense can be part of a few funds.

    When money is paid in to the club's coffers, the money can be allocated only to certain funds, like so:

    Code:
    _________________     _____________________________________
    |Payments        |    | Payments LINK Funds                |
    -----------------|    |------------------------------------|
    | ID             |    | PaymentID                          |
    | Date           |    | A FundID that this is allocated to |
    | Amount Paid In |    -------------------------------------
    | Member ID      |
    -----------------|
    This LOOKS good, but there's a problem I can't for the life of me work out what to do:

    How do I show how much money each fund has in it? Remember, some money might be allocated to Funds ONE and TWO, while other money might be allocated to Funds TWO and THREE...so in reality, is there a good way to show how much money is in Funds ONE, how much money is in Fund TWO, and how much money is in Fund THREE?

    The problem is, if the program simply shows how much money is in each fund, it will seem to the user that the club has far more money than it really has!

    By extension, the following issues also crop up:

    • What if some money isn't given to any funds...how will the program allocate it? How will the program show this money?
    • If someone wants to give money towards certain expenses which aren't grouped in one fund alone, should I create a 'custom fund' for them? If I put the money straight towards the expenses, I will violate database theory (I think) because some money will be allocated direct to expenses and some money will be allocated to Funds!


    I dunno, maybe I'm overlooking an obvious solution. Has this model been tackled before? Thanks in advance for any ideas.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    One way is to include an additional field in your expenses table specifying the amount - then have two rows, one for each fund. Another is to have a 'splits' table, linked to the expenses table and the fund table plus a column for the amount.

    this question concerns me

    What if some money isn't given to any funds...how will the program allocate it? How will the program show this money?
    the implication is that your app no longer suits your requirement and you need something more like a accounting system. Or as a minimum what is called a cashbook system)

    tblFunds
    FundPK
    FundName

    tblExpenseTypes
    ExpTypePK
    ExpType

    tblAccounts
    AccountPK
    AccountName (e.g. bank, cash)

    tblTransactions

    TransPK
    AccountFK
    ExpTypeFK (e.g. refreshments, build repairs, also include a 'transfer' so you can transfer amounts from one account to another)
    FundFK
    TranDate
    TranDesc (to expand on the expType, e.g. expType=Refreshments, trandesc=Coffee)
    TranAmount (in the accounting world this would be negative for deposits and positive for expenses, but doesn't really matter for your purposes)

    you can then determine what is left in a fund by adding up the transactions for a specific fundFK

    similarly, what has been spent on refreshments by adding up the transactions for a specific expTypeFK

    You can also use this to 'accrue' so for example you know fund1 is due to pay out another £40 on refreshments, but you don't know when - simply create the transaction, but leave the date blank. When the expense arrives, just edit the record and add in the date. It will still be included in your totals as above, but you can also identify it as a transaction that hasn't happened yet.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    A table containing all expenses along with the relevant information - fund, date, amount, expense type, etc.
    A table containing all payments and relevant information - date, amount, payor, etc
    A table containing allocations - payments will be allocated to an expense, here you need to track the expense ID, amount, date

    As you can see, funds can 'overlap', so an expense can be part of a few funds
    This needs to create multiple records on the expenses table - one record, one amount, one fund.

    How do I show how much money each fund has in it?
    All expenses tied to the fund subtracted from all allocations tied to the fund.

    What if some money isn't given to any funds
    This will be a payment received that isn't on the allocations table, or the total amount of the payment minus the allocated amounts.

    If someone wants to give money towards certain expenses which aren't grouped in one fund alone
    If, for instance, there is an expense which is common to three funds, then this one payment will have three allocations. Each of those expenses is a different record on the expense table and when you do the allocation you will select all three of them.

    There is also the issue of expenses and payments without a fund associated. You could create a fund called "General" or "Admin" or something and then your data will contain integrity. No payments of expenses will be left hanging.

  4. #4
    Join Date
    Jul 2017
    Posts
    6
    Quote Originally Posted by Ajax View Post
    One way is to include an additional field in your expenses table specifying the amount - then have two rows, one for each fund. Another is to have a 'splits' table, linked to the expenses table and the fund table plus a column for the amount.
    Sorry, not sure what you mean - I already made an Amount field in my expenses table. Can you elaborate?


    ALTHOUGH, I do like the idea of making "Expense Type" different to Funds, but I'm not sure what to make actual Funds and what to make Expense Types.

    Could you expand on these two points? Thanks in advance.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry, not sure what you mean - I already made an Amount field in my expenses table. Can you elaborate?
    Sorry, my typing - I meant a column for the fund

  6. #6
    Join Date
    Jul 2017
    Posts
    6
    Thanks. I do like the idea of making "Expense Type" different to Funds, but I'm not sure what to make actual Funds and what to make Expense Types.

    Could you expand? Thanks.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what sort of descriptions do you use at the moment?

  8. #8
    Join Date
    Jul 2017
    Posts
    6
    At the moment, the only way I categorize expenses is into FUNDS, as I wrote in my question:

    Expenses can be arranged into funds (like 'Expenses of year 2017', 'Food Expenses', 'Expenses of business meeting held to discuss premises' etc.).


    Now you're telling me to make two distinct things: FUNDS, and EXPENSE TYPE.

    Seems like a good idea, but when should I decide if something should be a FUND or something should be an EXPENSE TYPE?

  9. #9
    Join Date
    Jul 2017
    Posts
    6
    Okay, seems like Ajax has finished here...can anyone else point me in the right direction? Thanks in advance.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    seems like Ajax has finished here
    Sorry, busy with paying clients

    Seems like a good idea, but when should I decide if something should be a FUND or something should be an EXPENSE TYPE?
    From your description, I assumed that someone donates funds, or funds are allocated, for a specific or general purpose against which expenses are then incurred.

    If you are saying 'here is an expense' (expense) and it related to 'last year' (fund) then you can just use a datefield -to indicate which year it applies to. and if it relates to a 'fund' called 'Refreshments' then use the expense type field I mentioned in my first post.

    It may be terminology that is causing the problem - a fund is usually a reserve or allocated money that exists in a bank or cash account. I'm wondering if you mean budget (what you plan to spend in a future period) or forecast (what you now expect to spend in the current period) or a profit/loss account (what you have spent in a given period)

  11. #11
    Join Date
    Jul 2017
    Posts
    6
    Quote Originally Posted by Ajax View Post
    Sorry, busy with paying clients
    No need to apologize, we're here to help each other out of the goodness of our hearts, each time you reply it's only a favour

    OK, I thought "Funds" are what you really call "expense Types". I'll work on it and see where I get.

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

Similar Threads

  1. Tracking Continuing Education Funds
    By LeslieMB in forum Access
    Replies: 4
    Last Post: 03-11-2016, 09:49 AM
  2. Replies: 1
    Last Post: 12-13-2013, 07:11 AM
  3. Arrange subreport
    By Ray67 in forum Reports
    Replies: 1
    Last Post: 07-25-2012, 01:34 AM
  4. Replies: 1
    Last Post: 03-21-2011, 06:01 AM
  5. Calculating Old English Money
    By gbjc105 in forum Queries
    Replies: 4
    Last Post: 01-23-2010, 06:30 PM

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