Report summarising data from both a single and all child records on one layout….

Essentially I need a report from the context of the parent contact record so that the data extracted from child survey records can be sub-summarised by contact area and category. The problem is that the survey data for each contact needs to be summarised 2 different ways: sums from the most recent survey; and also counts from all surveys in the specified date range)... so essentially from two different relationships.

I have a working solution which basically involves adding a whole bunch of calculation fields and the summary fields into the parent contact table. When the global report start and end dates values are set, the date of the most recent report for each contact is calculated:

mostRecentSurvey = ExecuteSQL( "SELECT ''||surveyDate FROM surveys JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate

This is used as a match field to isolate the most recent survey record ( contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate ) from which I can pull the figures to be summed using simple calculation fields, e.g. result_Q1 = mostRecentSurvey::Q1

For the counts I used ExecuteSQL calculations rather than defining another relationship to pull in the appropriate results for each contact, e.g.:

surveysDone = ExecuteSQL( "SELECT COUNT surveyDate FROM surveys JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate

With the appropriate survey data pulled into each parent contact, it is then summarised using Summary (Total of) fields.

So, whilst I have a solution that works, it's a bit slow to update and I'm sure I'm missing something simple and there is a better, more efficient way to go about this.

Any thoughts or input would be much appreciated!