How to make a dynamic report using two tables with different setups

[CJ Jarvis]

Hello -
I am trying to summarize two tables with different setups in a report.

I am entering payments by periods.
Field names are as follows:
PaymentNumber = Payment 1, Payment 2, Payment 3..
Period = P1, P2, P3...
FiscalYear = 2010, 2011, 2012..
PaymentAmount = dollar amount for the period

For expenses I am entering by year.
ContractYear = Year 1, Year 2, Year 3...
ContractYearAmount = dollar amount for the year
StartPeriod = P1, P2, P3...
StartYear = 2010, 2011, 2012..
EndPeriod = P1, P2, P3...
EndYear = 2010, 2011, 2012..

Trying to sum the data in the following format:
Columns data = Thirteen periods (visible even w/o data)
Rows data grouped per year =
Expense 2010 [Summed]
Payment 2010 [Summed]
Difference [Expense-Paymnet]
-----------------------------
Expense 2011 [Summed]
Payment 2011 [Summed]
Difference [Expense-Paymnet]

I tried several ways, including a Union Query, then creating a Crosstab Query on this Union Query. One of the challenge arises because of the different ways in which data is entered for each table. Maybe I need to breakout the expense into a temporary period table(not sure how but just a thought)to match the payments table or is there a better easier way to accomplish this.
Thanks in advance for your help.
CJ

[beacon]

Hi CJ,

I think I understand what you're trying to do, but let me explain it back to you to make sure we're on the same page.

You have a report that you want to look something like the following:

                P1    P2    P3
Expense 2010   100   120    90
Payment 2010    50    75    90
Difference      50    45     0
 

If the above is fairly close to what you're trying to do, I've done something similar before. I created a cross tab query for each table individually and then created what ended up being subreports for a main report.

Then, in the main report, I reference the fields in the subreport to use in the Details section. From there, you can calculate the Difference by subtracting the fields you use for the Expense and Payments.

The reference will be placed in a text box and an example would be something like:

=[srptYourReport].[Report]![YourField]
 

If you name the textboxs Expense1, Expense2, Payment1, Payment2, then your textbox for the difference will be easy:

=[Expense1] - [Payment1]
 

Hope this helps,
beacon

[CJ Jarvis]

Thanks Beacon.
Your understanding of what I am looking for is right on par and your suggestion is definitely very helpful.

I am one step closer to resolving thanks to you but came across several obstacles that again leaves me road blocked. The issues that I pinpointed are:
1) if there is an expense and no payment, the total text field does not do the subtraction and give a difference total.

2) I am only seeing the output for the first year and nothing for the other years. In this case only for 2011.

3) I can't seem to get the report to show all the columns (periods) on one page.

I have attached my database that shows how I applied your suggestion with the hope that you could direct me where I went wrong for items one and two and a possible solution for three.
Thanks and really appreciate you taking the time and looking at this.
Regards,
CJ

[beacon]

Hi CJ,

1) if there is an expense and no payment, the total text field does not do the subtraction and give a difference total.

For this question, you need to add something to the expression to handle an empty/null field. Make the expression test for a value and if the value doesn't exist, make it zero.

2) I am only seeing the output for the first year and nothing for the other years. In this case only for 2011.

I can't really answer this question because I don't think your database is normalized. I think you need another table shows the relationship, that currently doesn't exist but probably needs to, between the expenses and the payments.

3) I can't seem to get the report to show all the columns (periods) on one page.

If your report is in landscape, you only have like 11 inches of horizontal space to work with, so if your fields aren't all showing on one page, you need to shrink the textboxes and the visual area of the report.

You could add a grouping for the year and move the year into the group, which would make your details section a little smaller, but after looking at your report, removing/moving one field won't make all of the fields show up on one page...you're still going to have to shrink some of the fields.

Hope this helps,
beacon

[CJ Jarvis]

Thanks again Beacon. Sounds good. I'm thinking of several workarounds based on your suggestions. I'll work on applying these over the weekend and see how it goes.

Best regards,
CJ