I have a somewhat complex related table problem;

At the moment, I have a table Sequences with data of the sort

[color:green]Patient - text

[color:green]Time - number

[color:green]Sample - text

[color:green]SeqLength - number

In [color:black]Sequences, there are often several entries that share the same [color:green]Patient, [color:green]Sample and [color:green]Time values.

I now need a new related table Visits that has a single record for every unique [color:green]Patient/[color:green]Time /[color:green]Sample combination. In the new related table, I would like to have a field [color:red]AveSeqLength that is the mean of all of the [color:green]SeqLength entries from matching records in Sequences. The Sequences table is very large, and I would like to avoid having to manually enter records into Visits but I am not sure how to do this. Also, I am not sure how to make the [color:red]AveSeqLength field.

Any thoughts welcome!

In your Visits table, define a calculated field AveSeqLength with the formula:

Average(Sequences::SeqLength)

That's the simple part.

Now you need to get a set of unique Patient/Sample/Time records in the Visits table. The simplest way is to generate them from the Sequences table. When you set up your relationship (based on Patient=Patient and Sample=Sample and Time=Time), check the "Allow creation of records in this table via this relationship" boxes in the "Edit Relationship" window. Then create this script and run it from the Sequences table:

Go to Record/Request/Page [First]

Loop

   Set Field [Visits::Patient; Sequences::Patient]

   Go to Record/Request/Page [Next; Exit after last]

End Loop

Edited April 12, 200619 yr by Guest

Thank you, this worked very well! Help much appreciated.

Marcel