AnsweredAssumed Answered

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 

 

 

 

Outcomes