# Create related table with average value

This topic is 5686 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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!

##### Share on other sites

• 2 weeks later...

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 by Guest
##### Share on other sites

• 5 weeks later...

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

Marcel

##### Share on other sites

This topic is 5686 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account