How to perform a calculation on a summary field
I have a table called 'Events' with three number fields: 'Event_ID', 'Event_Day' and 'Event_Minutes'. It also has a calculated field called 'Event_Times' which converts 'Event_Minutes' into a nice format including hours (for instance, if 'Event_Minutes' contains the number 75, then 'Even_Times' contains the string 01h15).
In order to get the total event times for a given day, I add a summary field called 'Event_DailyMinutes' based on the total of 'Event_Minutes'. Using a sub-summary based on 'Event_Day' and including 'Event_DailyMinutes' gets me what I want. Now, I also need a way to display the summarized information using the same nice format described above.
My idea is to create a calculated field called 'Event_DailyTimes' using the same calculation function as 'Event_Times' but applied to 'Event_DailyMinutes'. However, when I do so 'Event_DailyTimes' is not a summary field: when I drag it into the sub-summary part of my layout, I get for all days a single value corresponding to the total of all records in the table.
I suppose that there is no way for a field to be both a calculation and summary, and that my approach must be flawed. Can anyone suggest another way to achieve what I want?
Thanks in advance. W.