Thank you for your post.
Since you are getting January transactions, you probably have a link from your BUDGET table to your TRANSACTION table based upon Category and Month Number = 1. It appears you are getting your proper totals for that month, so I don't think this needs to be changed. True?
Pull down the File menu and select "Manage -> Database...". Click on the Relationships tab, and you will see a graphical representation of your BUDGET and TRANSACTION tables. At the bottom, there are a number of icons. Click the left icon that will add a new table. Select TRANSACTION table, and you will notice another table occurrence of TRANSACTIONS display, and titled "TRANSACTION 2". Using the same principles previously, set the relationship between BUDGET and TRANSACTION 2 based upon Category and Month Number = 2. This will allow you to retrieve the February totals, while also displaying the January totals.
You can then create the calculation to display the February totals and place this on the Layout.
Repeat the steps above for each of the months.
This should get you pointed in the right direction. If you need clarification for any of the above steps, please let me know.
There's not a way to filter them on the fly out of the original, single table? That's a lot of tables over the years.
Sorry. I assumed you would only have 12 months. You've just added another factor into the equation.
So, if you wanted January 2005 through December 2008, you want 48 columns for those four years?
The key field would then involve the year AND the month.
I can think of a way using scripts and global variables, but it would be too time-consuming for me at this time. Does anyone else want to help on this one? I feel like I'm overlooking something very simple.
Thanks for trying. I appreciate your feedback. I probably will only show the current calendar year (january to december) on certain layouts. I was just trying to avoid the manual intervention to create a new table each month. Thanks, again.
I have used TSGal's technique many times for columnar month-by-month analyses and it works a dream.
Instead of having 12 fixed 'Column Header Dates':
...and hence 2 sets of 12 derived fields:
Month ( 1/1/2008 ) etc
Year ( 1/1/2008 ) etc
- just set the one date for data entry: DateStart. Make it a global field.
- set all the others as the series:
- Year1 = Year (DateStart), Year2 = Year (DateStart + 30), Year3 = Year3 (DateStart + 60) etc
- Month1 = Month (DateStart), Month2 = Month (DateStart + 30), Month3 = Month3 (DateStart + 60) etc
- make them all global results
- make your relationships the same way, using these Year and Month calculations to link to the true Year and Month of the record
- Have the DateStart available for setting somewhere convenient, and set it to be the 15th of the month you want to start with. The data will all update to show any 12 months you want, even across years, just by changing one date.
- If you always want it to show the whole current year, or the next 12 months from now, or the last 12 months, or whatever, it is easy to have a script that will set those options either as buttons or on start-up.