Jump to content

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!

Link to comment
Share on other sites

  • 2 weeks later...

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


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]


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

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

End Loop

Edited by Guest
Link to comment
Share on other sites

  • 5 weeks later...

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

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.