Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts

    Trouble with complex calculation


    I have been working on creating an Access database that will calculate the relative dose intensity (RDI) of the chemotherapy regimens our patients receive. It is an equation that looks at the dose given and compares that to what the dose received and number of days between doses compared to what the ideal dose and time period would be. For example, the RDI would be less than 100% if the dose is reduced or if a dose is given later than the original plan. The equation is basically==> (Dose given/days between doses)/(Ideal dose/ideal days between doses). The RDI is calculated for each drug, then if more than one drug is given, the average of all the RDI's is determined for the total RDI for that patient on that chemotherapy regimen.

    My current problem is for regimens that do not give all of the drugs for the same day. For example, in Rituximab+Bendamustine+Velcade, the Rituximab is given on day 1, the Bendamustine is given on days 1 and 2, and the Velcade is given on days 1 and 8 of a 28 day cycle. When the database calculates the total RDI, it gives me multiple final numbers. This appears to be happening due to the entries for doses on day 2 and 8 creating separate reports for those entries.

    I have two regimens in the database that have this problem (the one mentioned above and R-CHOP+Velcade). I have attached the database (Outlook 2007) with the patients names and medical record numbers changed to prevent any HIPPA violations. Can anyone give some suggestions on how to work around this issue? It is beyond my limited knowledge of using Access. Or, if there is an easier way to do the calculations than the multiple queries I have set up, I'm more than willing to learn!

    Thanks in advance for any and all input!
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    There are a whole raft of issues raised here.

    I can't do something in Access unless I could do the calculation outside of Access first.
    In this case I don't think I could.

    You have told us:
    (Dose given/days between doses)/(Ideal dose/ideal days between doses)
    and yet the problem occurs when not all doses are given on the same day.

    Yet with the formula above, "days between doses" would be zero if the doses on the same day , so you would have a zero denominator. But maybe what is meant by on the same day needs to be clarified.

    But the biggest issue is getting the structure of the database correct before trying to work out these calculations.

    The most important rule of database design is (I think) this:
    Whenever you have multiple of those of each of these, they need to go into a separate table.

    What you don't do is just add a bunch of repeating fields instead. But you have done that twice.

    Each Regime involves multiple drugs, so you need a RegimeDrugs table to record all the data you currently repeat.
    Each record in tblData has repeating fields for various drugs. I would need rename tblData to tblTreatments, then move most of it to tblTreatmentDrugs. Get rid of all repeating fields.

    After restructuring the tables you need to look at the Relationships.
    All the multiple copies of the same table can go. You also have the wrong connection between tblPatients and tblData. You have joined the two keys, rather than the key field of tblPatients to the PtName field (really a number - see below) in tblData.

    The PtName field in tblData is actually the Patient ID field, but setup to hide the number and show the name. That is confusing. Rename it, and stop hiding the number. When you look in tables you should see what is really there. Forms and Reports can massage the data to make it look better, but not tables.

    It is good to use autonumber fields as the primary keys for tables, but don't just call them ID. It becomes very confusing when you have multiple fields called ID. PatientID, RegimenID etc.

    You don't need to use Group By in queries to be able to perform a calculation. qryBSA looks like this.

    qryBSA.gif

    It can be just this. You don't need any Group By's at all.
    qryBSA2.gif


    It really is worth trying to fix all this, and there is not much point trying to work out this calculation until it is fixed.
    Regards
    John



  3. The Following User Says Thank You to johnhutchison For This Useful Post:

    kyhawkeye (2011-09-22)

  4. #3
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I appreciate the input. I thought I might have had some 'overkill' with some of my tables but was unsure of the best way to set things up.

    So I make sure I understand you correctly, I should not have a separate table for the drugs, but just have each drug entered as part of the Regimens table? In other words, I create the regimens in the table, listing each drug there for each specific regimen rather than populating it from a separate table. Thus, I pull the 'drug' name from one table and not bounce from table to table.

    " I would need rename tblData to tblTreatments, then move most of it to tblTreatmentDrugs. Get rid of all repeating fields."

    I'm confused on this sentence. So, I rename the tblData, then transfer the content to a different table as stated above. What do I do with the newly renamed tblTreatments? Or will that be defined in "Part 2" of this adventure?

    I'll fix the 'group by's. I thought that was needed required.

    The ID was the default that came up for each table as the primary key. I'll fix those as well to reflect the actual table.

    Please pardon my if any of my inquires seem to fall in the "dumb and stupid" question department. I just want to make sure I'm understanding you clearly so I can do it right.

  5. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by kyhawkeye View Post
    So I make sure I understand you correctly, I should not have a separate table for the drugs, but just have each drug entered as part of the Regimens table?
    No I think that is the exact opposite of what I am suggesting.

    Within tblRegiment you have room for 8 drugs by repeating Drug, CalDose,NumDosesand FlatDose 8 times in the fields list.

    You should remove all these fields, and instead create a table tblRegimenDrugs with just these fields:
    RegimenDrugID, RegimentID, DrugID, CalDose,NumDosesand FlatDose

    TblData (aka tblTreatments) repeats Drug and Dose 8 times. Remove those and create a table tblTreatmentDrugs
    TreatmentDrugID, TreatmentID, DrugID, Dose.

    So my Relationships Diagram would be something like this.

    PatientTreatmentsRelationships.gif

    Now I don't know whether that is correct yet, because I don't understand enough about Treatments and Regimens and how they relate to Patients and each other. Maybe some fields are in the wrong place perhaps.

    It worries me that tblTreatments has a Cycle field. What does that mean? Perhaps a Treatment includes multiple cycles. In that case we would need yet another table tblTreatmentCycles that would go in between tblTreatments and tblTreatmentDrugs, and would include the Cycle and the Treatment Date.
    Regards
    John



  6. The Following User Says Thank You to johnhutchison For This Useful Post:

    kyhawkeye (2011-09-22)

  7. #5
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Good thing I asked. I thought maybe I was going in the wrong direction, esp. since I was sick last night and was running on low sleep when I read your reply this afternoon.

    Yes, usually for each patient they will get multiple cycles of the same regimen. For example, for R-CHOP (used for lymphoma) they normally get 6-8 cycles of treatment. Some regimens are just a single cycle, but most average at least 4 cycles of treatment.

    So, for a patient getting R-CHOP, they would get each drug on the first day of each 3 week/21 day cycle (Length in the tblRegimen representing the number of weeks, since the treatments are measured in numbers of week, normally). I have considered redoing it to the length being number fo days, with the problems I'm having with regimens such as Rituximab-Bendamustine-Velcade as mentioned in the original post.

  8. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    To get the database right you need a good understanding of how the treatment system works, and what you want out of the database, and I don't have either of those.

    I would have thought a Treatment meant a Course of treatment, based on a Regimen, so the various Cycles would all make up the Treatment.

    So you could put in a TreatmentCycles table that sits between tblTreatment and tblTreatmentDrugs. Or perhaps just move the date and cycle fields into tblTreatmentDrugs. I am not sure what is best.

    When a treatment involves multiple cycles and multiple drugs, does each cycle always include all drugs? I would have thought not (but I don't pretend to know).

    So somewhere, and I don't know where, do you need info to allow you to work out when to administer each drug as part of a cycle. I can't see where it is at the moment.

    Do you use this database to tell you when to administer the drugs? I am wondering whether you need to create records with a datedue field, then some process for telling who needs treatment today.

    Back at the beginning you made reference to : Ideal dose and ideal days between doses. Where are they?
    Regards
    John



  9. The Following User Says Thank You to johnhutchison For This Useful Post:

    kyhawkeye (2011-10-04)

  10. #7
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    I finally have the tables set up as you suggested and reworked the queries to reflect the new tables and now everything is working exactly as I need. Took me a little while to double check all the math, but my testing shows that all is correct.

    A big thanks for all of your help, John!

    Here is a look at the final product if you are interested.
    Attached Files Attached Files

  11. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad I could help.

    I was not sure what to do with the attachment. It is a text file.
    Regards
    John



  12. #9
    Star Lounger
    Join Date
    May 2003
    Location
    Whitmore Lake, MI , USA
    Posts
    84
    Thanks
    13
    Thanked 0 Times in 0 Posts
    Oops. I have to send zip files back and forth from work to home renamed as txt files otherwise the system filters out the email message or refused to send it. Just rename it as a zip file and the database is inside.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •