Stuck on a summary/calculation problem

[E Kelly]

I'm not sure what I want to do is possible with FM, but here goes:

I have a summary report subsorted first by therapist, then by month, in a Notes table that adds up therapist hours based on the billable time they entered for each note. On the month subsummary part, along with the sum of direct service hours, I have a field from a related table, Paid Time Off. The PTO table has a separate record for each therapist for each month, with their PTO time for that month. I have also created in the Notes table a field that totals the therapist's hours for the month like so:

Sum(Therapist by Month::Hours Total) + PTO::PTO Hours

The Therapist by Month is a self-join relationship in the Notes table based on the month of the note and the therapist ID, and the PTO relationship is keyed on the same field.

So, on my month subsummary part I have:

Direct Service Hrs. | PTO Hours | Total Hours

So far so good. However, I'd like to have a grand summary part that gives a total of all these columns. Because the report could be run for one month or 20, I can't base my summary fields for the grand summary on the month. Doing a sum field based on Total Hours gives me garbage.

Anybody have any ideas? I'm stumped.

TIA

Eric Kelly

[David Jondreau]

Are you using summary fields or calc fields? With all these self joins, I'm guessing you're using calc fields to to what summary fields should be doing.

Try creating a field of type Summary that is a Total of Direct Service Hours. Place that field in a sub summary part. When the records are sorted by the same criteria you've defined in the layout part definition, the totals will show.

You can place that same field in a Trailing Grand Summary for the total hours.

[E Kelly]

I think maybe I didn't explain the problem very well (or there's some simple solution I'm not getting -- always a strong possibility!).

The reason I'm using the calculation field with the self-join in the Notes table is to get the information from the two tables into one field. I could certainly make the therapist direct service hours be a summary field of all the note records sub-summaried by month, but then I have no way of adding in the hours from the PTO table for that month, which is just going to be from a single record for that therapist for that month.

Hope this makes sense -- and if I'm missing something, please bring it to my attention!

Eric