471,121 Members | 1,246 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,121 software developers and data experts.

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

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
Jan 29 '11 #1
4 2674
beacon
579 512MB
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:

Expand|Select|Wrap|Line Numbers
  1.                 P1    P2    P3
  2. Expense 2010   100   120    90
  3. Payment 2010    50    75    90
  4. Difference      50    45     0
  5.  
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:

Expand|Select|Wrap|Line Numbers
  1. =[srptYourReport].[Report]![YourField]
  2.  
If you name the textboxs Expense1, Expense2, Payment1, Payment2, then your textbox for the difference will be easy:

Expand|Select|Wrap|Line Numbers
  1. =[Expense1] - [Payment1]
  2.  
Hope this helps,
beacon
Jan 31 '11 #2
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
Attached Files
File Type: zip Summary.zip (124.8 KB, 112 views)
Feb 2 '11 #3
beacon
579 512MB
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
Feb 3 '11 #4
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
Feb 5 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Climber | last post: by
2 posts views Thread by zoro25 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.