AnsweredAssumed Answered

Autoenter looked up value

Question asked by mark_d2x on May 5, 2012
Latest reply on May 8, 2012 by philmodjunk


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


Any suggestions?