Help with consolidated monthly income and expense report
We manage properties as short term vacation rentals and report to our owners monthly for expenses incured and revenue generated. I have no problem generating monthly expense and revenue reports based upon the respective tables however I can't figure out how to create a report that shows consolidated monthly revenue and expenses. My preference is to be able to generate this monthly profit/loss report in FMP instead of exporting the records into Excel.
The Properties table has a one to many relationship to the Expenses table AND a one to many relationship to the Reservations table. (One property has many expenses and One property has many reservations). The foreign key in both the expense and reservations table is the _kf_PropertyID. Both the expense and reservations tables have fields that calculate the month number, the month name, and the year for each record from the expense date and reservation date fields, respectively, for sorting into year and month in subsummary reports. Fields for total expenses and total reservation revenue are also in the repsective tables.
Properties:_kp_PropertiesID = Expenses: _kf_PropertiesID
Properties:_kp_PropertiesID = Reservations: _kf_PropertiesID
I suspect the answer is a simple one and any assistance is greatly appreciated. I use FMP12 Advanced.