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
pk_ch_ID12
WordCount10001200
WC_sum10002200
pk_sess_ID12
fk_ch_ID12
minutes1015
minutes_sum1025
WPM10080
average_WPM10088

 

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.

Outcomes