AnsweredAssumed Answered

Calculation - maximum value

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


Calculation - maximum value


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



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.



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