Autoenter looked up value
In my prostate cancer databse i want to look up the PSA value for a particular follow up for the patient. The dates may not be exactly the same. In this database the relationships are:
Tumour -> one to many -> PSA (kp_TUM_GEN = kf_PSA)
Tumour -> one to many -> Follow up (kp_TUM_GEN = kf_FOLLOW_UP)
I have set up a new entity of the PSA and Follow up tables so that PSA2::kp_TUM_GEN = FOLLOW_UP2::kp_TUM_GEN and PSA2:DATE_SAMPLE = FOLLOW_UP2::DATE_OF_FOLLOW_UP. (The kps are alphanumeric - kp_TUMOUR_000000000001 etc)
In the FOLLOW_UP table want to look up from the PSA table the PSA result or the one with the next lowest date.
So, the lookup is for FOLLOW_UP::PSA -
Starting with table: FOLLOW_UP2 |looked up from related table PSA2 | PSA | If no match, then copy next lower value
I think I'm almost there but not quite.
Where there is no match I am just getting a "?" not the next previous date value.
In a different way:
01/01/2012 PSA = 10
01/02/2012 PSA = 5
01/03/2012 PSA = 12
If a patient was seen on
02/01/2012 PSA = 10
02/02/2012 PSA = 5
02/03/2012 PSA = 12