Repeating calc to sum categories

[mr_vodka]

Here is the general situation.

There are two tables Project and Charges. Each cost is associated to a project by ProjectID. Each charge also has a account code ( range is from 000, 100, ... 900).

Now I have to sum up by each code for each project. IOW, I need to know the total charge amounts for each one.

The easy way of doing this would be to create a global calc for each account code and then I can get a sum for each one or I can create an intemediary table with 10 records; one for each code, set a global field with the ProjectID and sum it up that way.

All that being said, I wanted to make my life difficult and thought that perhaps this can be done with a repeating calc. I can create a calc that gives me the account codes easily enough:

Right ( Get ( CalculationRepetitionNumber ) * 100; 3 )

But perhaps there is a way to individually sum up the charges for the project by each account code as well.

At this point, my mind is elsewhere and I need a second opinion to see if I am just chasing my tail.

[comment]

Hi John,

these two examples are very close to what you describe:

http://fmforums.com/forum/showpost.php?post/165564/

http://www.fmforums.com/forum/showpost.php?post/182999/

There's also a detailed tutorial on Mikhail Edoshin's blog:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

[mr_vodka]

Thank you Michael. I will look into these and see what I can extract. Although I have read Mikhail's Cross tab report previously, somehow I wasnt thinking about it. :o

[mr_vodka]

I looked at Mikhail's blog again and it did not help as much as much as your "Counting Sheep" example.

http://fmforums.com/forum/showpost.php?post/165564/

Here is a modified version of your file with my changes.

sumByCategory_Modified.zip