# Calculation - maximum value

Question asked by mark_d2x on Feb 24, 2010
Latest reply on Feb 24, 2010 by philmodjunk

### Title

Calculation - maximum value

### Post

Hi again,

I have a few calculation questions for a medical database;

There are 4 staging examinations a patient can have prior to having the surgery; each of these results is stored in separate fields CT_STAGE, CT_RESTAGE, MRI_STAGE & MRI Restage.  The lookup is a separate table and there is a relationship set between the main table and the lookup field.  Unfortunately, the values are not straightforward;

Here is how the table looks so far:

Data Code   Code

TX 1

Tis  2

T0 3

T1 4

T2 5

T3 6

T4

I added the code so that they are in the correct order (nb these are not all the rows - there are 12).  The same lookup is used for each of the 4 fields with separate instances of the lookup table in the relationships graph.

The  results may be:

CT Stage   T3

CT Resage T2

MRI Stage T4

MRI Resage T3

So out of these 4 results I need T4 to be the result.  What is the best way to do this as a calculation?

Also, within another table I have a similar problem this time just with a single field.  A patient may have multiple biopsies or minor resections of the tumours prior to having the final major surgery; I am using the same treatment table for all the treatments including the final surgery.  Ideally, I want to keep the fields the same for all these treatments.

eg.

treatment 1   T1

treatment 2  T2

treatment 3 Tis

treatment 4 T3

treatment 5 T2

major resection T4

So the result I need here is T3, but I need the maximum excluding the current record.

The final one is one I asked previously but never managed to sort out;  i have time started and time finished set as time fields.

so if a procedure starts at 23:50 and ends at 00:10 the result i need is 20.

I thought if i could use

(time start - time finish) + (60x24) (sixty minutes in an hour 24 hours per day)  Timestamp was suggested but the problem with that is that some of this is historical data and would be a nightmare to change to timestamps.

Any other suggestions?

best wishes

Mark