Thread: Medical Database Question
05-03-06, 11:30 #1Registered User
- Join Date
- Feb 2006
Unanswered: Medical Database Question
Good morning. I'm new to Access, (I’m using Access 2003) but have been thrown into developing a database system. Although I've done a bit and have the basic format set, my boss has just asked me to do something a bit more complex. In particular, I’m work on a medical database and need to create a variable called “follow-up” that is based on other fields.
Specifically, “Follow-up” is defined as:
1). Time to biochemical failure. Biochemical failure can be one of three things:
a) Date where there was any value over 0.2 on a blood serum test (Prostate Specific Antigen test). I have a separate table that links PSA values to specific dates and patients.
b) Date where there two consecutive values of exactly 0.2 on the PSA test
c) Date where there are any value over 0.0 occurring 6 months after either hormonal or radiation therapy. I have two fields for “date when hormone treatment started” and “date when radiation therapy started.”
2). If there is no biochemical failure, the date of the most recent PSA test
The “follow-up” variable would indicate the number of months between the date of surgery (“DOS” variable) and the “follow-up” criteria listed above.
I’m thinking that I can build an update query, but I am having trouble conceptualizing how to do so. Any advice/direction would be greatly appreciated.
05-03-06, 13:46 #2Registered User
- Join Date
- May 2004
- New York State
I think you're on the right track, wanting to do it with an update query. However, your criteria are so complex, if you do it with pure SQL it'll be next to impossible to maintain.
By the way, you didn't mention this point, but I understand that if the last PSA test was AFTER biochemical failure, the operative date in that case would be the test date. Whether right or wrong, it's something that has to be considered and programmed for.
If I were you, I'd do this part of the program in VBA. First look in your table (using If or IIf statements) for any of the criteria being true; if any are true, set a VBA variable to true and record the date in another VBA variable. If more than one is true (I don't know if it's possible), you have to make a decision whether to take the earliest date or the latest. After comparing all the criteria, compare that date (if any) to the last PSA test date and determine which date you want (refer to the previous paragraph). THEN have an update query updating the correct field with this date.
Hope this gets you on a good track,
05-03-06, 15:14 #3Registered User
- Join Date
- Apr 2006
- Huddersfield, UK
I have created a similar DB for a complimentary therapies project that used follow-up and the simplest method i employed was:
use queries to set the parameters for neccessary follow up: or if follow-up is necessary at all.
Then have the user input a follow-up date (if this field is blank the query wouldn't pick it up and therefore not issue reminders/ask for further info)
you could have a listbox for followup type (if needs be).
Your query can set reminders if forms need to be sent out to people for follow up purposes, based on the follow up date (either using the date directly, or in the case of multiple follow ups, on a time delay from date of first follow up)
Have a seperate table to handle the new follow up information and link it to the patient. you may well have:
all these would be directly linked to the main paintent_info table.
It is all possible to do this through the main access front, without doing back end code (i dont know any back end code) and still make it work and run smoothly.
in the tbl_patient_followup id have the following fields
patient (linked to tbl_patient_info)
value over 0.2 date:
two consecutive values of exactly 0.2 date:
value over 0.0 occurring 6 months date:
if none most recent test date:
doing it this way would enable multiple follow-ups per patient, especially useful if they undergo several courses of treatment and several follow_ups and if this could be the case, then an additional field to add a number of the record in would be useful for chronology.
hope this helps,
Dansometimes simple is best.... and i'm just a simple fellow.
05-03-06, 18:14 #4Super Moderator
- Join Date
- Jun 2004
- Arizona, USA
Curious. Is there an overriding reason that you're using Access in a critical app, rather than a more robust, more secure alternative (MSDE, PostgreSQL, SQL Server...?)