How to create a custom crosstab report
Posted on 2011-02-14
I am using Access 2003 to create a database to track contracts. I would like to create a report that would like to look something like this:
P1 P2 P3
Expense 2010 100 120 90
Payment 2010 25 50 75
Difference (75) (125) (150)
The report would then list 2011, then 2012 below in the same format.
I have a Contract table with a unique ContractID key linking to a LineItems table in a one to many relationship. Then have ExpenseDetails table and PaymentDetails table linked to LineItems via a LineItemID field in a one to many relationship.
Does anyone have an idea how I can accomplish this? I tried crosstabs but could not get the row to subtract payments from expenses. Your help is deeply appreciated as I have been trying to resolve this for over a month now and no results. Please let me know if I need to provide additonal information to give better clarity.