Income/Expense report - layout or structure problem?
I read a post by PhilModJunk on income/expense reporting but I'm stuck on how I could make my solution work with a lineitems type of table that Phil suggests. (the post: http://preview.tinyurl.com/cap3p96)
From what I understand from Phil the basic database structure should look something like the screenshot I have attached.
I'm in the events business, and during an event I have a lot different types of income & costs and at the end of the night I would like to know the profit/loss I have made.
On the income side I basically have: bar takings, entrance tickets, cloak room takings.
On the cost side I have: venue rent, insurance, artist cost etc.
The problem arises when I try to house these incomes and costs in two tables: 'incomedetail' and 'costdetail' and link them to my events table through a cost/income line items table and use that line items table as a portal on my event layout. My sources of income aren't just final amounts that I can type into the field corresponding to the source of income.
For instance for my bars I just need to type in the total amount: bar1cashier1total bar1cashier2total bar2cashier1total etc where these would be the total amounts of cash typed into the cash registers.
For the entrance I need to have: entrance1price1, entrance1price2, entrance2price1, entrance2price2, entrance1amountsoldprice1, entrance1amountsoldprice2, where the total amount of cash generated is amount sold x type of ticket.
All my sources of income are calculated in a different way, therefore not allowing for a straighforward lineitems setup where you choose your type of income and just fill in the amount.
This quickly becomes a very long list of different types of income. In total I have about 43 different sources of income on one event all calculated in a different way.
I don't see how I could link the 'incomes' table to a 'lineitems' table and have that line items table appear as a portal on my 'events' layout without there being just too many fields on the lines portal in the limited space the portal row will offer me. I need to be able to house 43 different types of income on one layout. I fear a portal can never work taking this into account. Do I need to rethink my database and create different tables for all my different sources of income and have their fields appear individually on my Events table instead of working with a portal? If that's the case, I'm worried about how I'll be able to run a report to calculate my profit/loss for the event.
Any help is more than welcome.