Calculation - maximum value
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
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?