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)
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_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".
|Field||Record 1||Record 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.