AnsweredAssumed Answered

Use "GetSummary" with field in different table

Question asked by wladdy on May 26, 2016
Latest reply on May 26, 2016 by erolst

A database records the lengths of various book chapters, and the time it takes for a reader to read the chapters.


Table "Chapters" has three fields:

pk_ch_ID (primary key)

WordCount (integers)

WC_sum (summary field with the running total of WordCount)


Table "Sessions" has five fields:

pk_sess_ID (primary key)

fk_ch_ID (foreign key)

minutes (integers)

minutes_sum (summary field with the running total of minutes)

WPM (calculating words-per-minute: WordCount/minutes)


"Chapters" and "Sessions" are related through a one-to-many relationship between "pk_ch_ID" and "fk_ch_ID", which are also the fields sorting their respective tables in the relationship.


I want in "Sessions" a calculated field called "average_wpm" with an expression calculating "chapters::WC_sum" divided by "minutes_sum".

For example:


FieldRecord 1Record 2


However, before even getting into the division, I cannot find a way to properly use the "GetSummary" function in order to get "chapters::WC_sum" into a layout based on "Sessions".


I tried to use "fk_ch_ID" both as the break field in GetSummary (chapters::WC_sum;fk_ch_ID) and the field sorting the layout. To my surprise, this does not work and "average_WPM" remains blank.

I tried many other combinations, but none works as I want.


Obviously, I am missing basic paradigms on the meaning of sorting (for instance, I don't get the need of a sort order for the relationship when the layout has its own), the working of summary fields and the proper use of a break field. I feel that my database is not properly designed, but I don't understand why.


I would appreciate immensely links to ressources that could enlighten me in these areas, as well as a practical solution for my problem at hand.


Thanks in advance! W.