Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Question Unanswered: The initial design for Income and Expenditure

    Hi,

    I would be very grateful if anyone could advise the best way to design an Access database to record Income and Expenditure???

    Is it best to have 2 separate Tables to record the INCOME and the EXPENDITURE???


    1. My thoughts are that I need 2 different Tables:-
    Income Table
    Expenditure Table

    2. Therefore, I will need 2 different Forms:-
    Income Form (to record money coming in such as rental income, annual management fee, commission, advance payment for repairs etc)
    Expenditure Form (to record money paid out such as payment for repairs, taxes, association dues etc)

    3. Therefore, to produce a Report of all the INCOME and EXPENDITURE (to see the overall Profit/Loss), I will need to merge these 2 tables together before I produce the Report?

    I was wondering if this is the best way, or if I am missing something a lot more easier?

    Thanks in advance.
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm always hesitant to take a position on "best", but I lean towards a single table with positive and negative amounts. I would only use 2 tables if the fields required for each category differed significantly.
    Paul

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    pbaldy,

    thanks very much for your input. i really cannot decide which woudl be 'best'.

    1. If there was only 1 Table then how could the positive and negative amounts be recorded on the Form??? I wouldn’t want the User to have to remember to put a “-“ sign in front of every expenditure.

    2. And if there were 2 Tables then I would have approx. double the number of Fields for income and expenditure. And I think I would have to Join the 2 Tables together (so this coudl be the same size as 1 Table only?) to get a report for profit/loss?

    Examples are:-

    Date Money Received
    Date Money Spent

    Amount Received
    Amount Spent

    Money received to pay for maintenance work
    Money spent for maintenance work

    Etc etc.
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, if you use two tables you end up joining them back as if they were one anyway, so I'd prefer one. You can let the user enter all positive numbers, and flip the appropriate ones to negative based on what account they choose. Or you can record everything as a positive, and treat expenses as negative on your report, like:

    Income item 1......$10
    Income item 2......$20
    Total income............$30

    Expense item 1.....$5
    Expense item 2.....$10
    Total Expense..........$15

    Net Income = Total Income - Total Expense
    Paul

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    another way of modelling the single table approach is to have a Transaction Type (or payment Type)
    Code:
    TransactionTypes
    Type String(3) PK
    Description String(50)
    Effect Integer
    Effect is either + or -1.. if its plus then the transaction has a positive effect on the balance, if -1 then its a negative effect. joining the transaction table to the TransactionTypes using the Type as the foreign key and multiply by Effect.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I like that idea.
    Paul

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your suggestions, guys.
    Sorry healdem, I didn't understand your coding. Do you mean have an additional Field with Transaction Types (eg. Income or Expenditure)?
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    income and expenditure are effectively the same beast (except income adds to ytour balance, expenditure reduces the balance. adding a transaction type table allwos you to have a single table with the addition of the transaction (payment type) identifying what type of payment it is. ths transaction type is the FK to the table suggested above

    ferisntsance it could be a cheque, cash, TTFT, heck you coudl even set up different rtransaction codes for, say two accounts. if you had more than two accounts then you'd be better off addign another table 'Accounts'
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    I am still not sure if I have got exactly what you mean?

    Is your suggestion to have the following Fields on a Finance Form?

    FinanceID
    AmountOfMoneyReceived
    TransactionType ComboBox (eg. Rent Received, Deposit Received etc (for Income) and Repair Costs, Electricity etc (for Expenditure)

    These TransactionTypes would be stored in a separate Table and be separated into income and expenditure items?

    The TransactionType ComboBox on the Finance Form would have all the choices whether it was income or expenditure?
    If an Income choice was made then ‘somehow’ the AmountOfMoneyReceived would be made a credit, if not then it would be a debit?

    Please would you confirm if that is how you are suggesting?
    What would you attempt to do if you knew you would not fail?

Posting Permissions

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