Table relationship and summary problems
Table relationships in an event mgmt system …
tblProjects --- __kp_ProjectID .
tblDeals --- __kp_DealID, _kf_ProjectID [related to kp_ProjectID above]. Multiple deals per Project.
tblEvents --- __kp_EventID, _kf_DealID [related to kp_DealID above], _kf_CompanyID [venue where Event takes place]. Multiple events per Deal.
tblEventBoxOffice --- __kp_EventBoxOfficeID [uncertain if I need this], _kf_EventID [related to tblEvents:: EventID]
This table has one to five ticket prices, and their relevant capacities and tickets sold [as records] per EventID. Relevant fields, all numbers --- TicketPrice, Capacity, TicketsSold. [There are other calculated fields that accommodate Price * TicketsSold, etc.]
tblEventExpenses --- __kp_EventExpenseID [uncertain if I need this], _kf_EventID [related to tblEvents:: EventID]
This table has numerous expenses per EventID. Each has a category – ie advertising, insurance, etc.
Relevant fields --- Category, Description [<both text] FlatRate, VarPerHead [<both number] and Amount [calculated --- FlatRate + Sum(tblBoxOffice::TicketsSold) * VarPerHead . This is *not* working ].
Projects and Deals are working correctly. Have run into roadblocks with the Event components.
Have an EventDetail layout where …
tblEventID:: EventID & DealID fields are up top.
Portals to tblEventBoxOffice, showing each event’s prices/etc *and* tblEventExpenses showing each event’s expense & category lineitems make up the rest of the main body.
*Have shortened the table names below for ease of use. They are in reality exactly as above.
- Need to sum tblBoxOffice and tblExpense records by EventID on the EventDetail layout. Do I need to create related summary tables to do this?
- A curveball --- Some expenses are variable, such as insurance which is calculated as say, $1 for each ticket sold. So a calculated field named Amount in tblExpenses needs to have the formula tblExpense::FlatRate + sum(tblBoxOffice::TicketsSold) *tblExpenses:: VarPerHead working properly. Let me know if this is clear enough.
- Need to have every record from tblEvents::__kp_EventID show up automatically in the BoxOffice and Expense tables. Have tried AutoEnter Looked Up Value, but the fields don’t populate. Any ideas as to what I’m doing wrong? Are script triggers the solution?
- Further down the road, need to have the ability to copy an entire Deal – containing tblDeals:DealID and *every* relevant record from the tblEvents:: schema, into new records in the relevant tables. Believe this one can be put aside for now, just thinking ahead and don’t want to back into a corner that can’t be reversed. Have already done a few dozen of those …!!
Wrestled with numerous relationships/ layouts and have hit a wall where some help is necessary.
Extreme and sincere thanks in advance for any assistance with the above.