424,493 Members | 1,351 Online
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

# Creating a chart from accumlated datasets.

 P: 5 My son is a diabetic. I am creating a database to help me manage his disease. He get's insulin through a pump that delivers "basal" insulin (small automatic injections spaced out to support basal metabolism) and "bolus" insulin (one time injections given at meal times). The insulin has a half-life of 81 minutes. To make things a bit more complicated a "bolus" can be "normal" (all insulin given at once) or "combo" (some insulin given up front & some given over time - like additional basal insulin). His pump can also deliver additional basal insulin. Thus, total active insulin is a result of basal insulin, temp basal insulin, normal bolus insulin injections & the extended combo insulin. There is only one "combo" and one "temp basal" that can be active at any one time but there can be multiple "bolus" injections. I have tables that store the basal, temp basal, and bolus information. Where I am needing help is in that I want to create a curve (chart) showing active insulin using the discreet inputs and a decay rate. Any ideas on what would be the best way to approach this would be much appreciated. Oct 28 '13 #1

Let's start simple since this is such a large topic.

Given a table that holds injection information.
Injections
Expand|Select|Wrap|Line Numbers
1. InjectionTime              InjectionAmount
2. 10/28/2013 7:00:00 AM      100
3. 10/28/2013 9:00:00 AM      10
4. 10/28/2013 11:00:00 AM     10
And given a table that holds a list of time intervals at which you want to see how much insulin is left.
TimeTable
Expand|Select|Wrap|Line Numbers
1. TimeElapsed
2.
3. 40
4. 81
5. 120
6. 243
And given this formula for half-life I found on wikipedia:
`Insulin after t minutes have elapsed = (Insulin injected) * (1/2) ^ (t / 81)`

Then all you need to find the amount of any shot at any time is this query:
Expand|Select|Wrap|Line Numbers
1. SELECT
2.      Injections.InjectionTime,
3.      Injections.InjectionAmount,
4.      TimeTable.TimeElapsed,
5.      IIf(DateAdd("n",[TimeElapsed],[Enter Report Start Date Time])>[InjectionTime],[InjectionAmount] * (0.5 ^ (DateDiff("n",[InjectionTime] ,DateAdd("n",[TimeElapsed],[Enter Report Start Date Time])) / 81)), 0) AS InsulinRemaining
6.
7. FROM
8.      Injections, TimeTable
9.
10. WHERE
11.      Injections.InjectionTime Between
12.      DateAdd("h",-5,[Enter Report Start Date Time])
13.      And DateAdd("n",243,[Enter Report Start Date Time])
14.
15. ORDER BY
16.      Injections.InjectionTime,
17.      TimeTable.TimeElapsed;
In the WHERE clause, I subtract 5 hours to include all shots given within the duration of action. And I project out 243 minutes to collect all shots given within the range of the time table.

The results of this query, given a report time of 10/28/2013 10am, would be:
Expand|Select|Wrap|Line Numbers
1. InjectionTime     InjectionAmount     TimeElapsed     InsulinRemaining
2. 10/28/2013 7:00:00 AM    100    0    21.4310995713268
3. 10/28/2013 7:00:00 AM    100    40    15.219054283899
4. 10/28/2013 7:00:00 AM    100    81    10.7155497856634
5. 10/28/2013 7:00:00 AM    100    120    7.67492411506507
6. 10/28/2013 7:00:00 AM    100    243    2.67888744641585
7. 10/28/2013 9:00:00 AM    10    0    5.98432013073045
8. 10/28/2013 9:00:00 AM    10    40    4.24969762371262
9. 10/28/2013 9:00:00 AM    10    81    2.99216006536523
10. 10/28/2013 9:00:00 AM    10    120    2.14310995713268
11. 10/28/2013 9:00:00 AM    10    243    0.748040016341306
12. 10/28/2013 11:00:00 AM    10    0    0
13. 10/28/2013 11:00:00 AM    10    40    0
14. 10/28/2013 11:00:00 AM    10    81    8.35516799030218
15. 10/28/2013 11:00:00 AM    10    120    5.98432013073045
16. 10/28/2013 11:00:00 AM    10    243    2.0887919975755
This detail can then be aggregated on the TimeElapsed and summing on InsulinRemaining to provide the data points for a chart report in Access. Start with this as a base, validate the results, and build outwards from there.

10 Replies

 Expert Mod 10K+ P: 12,315 We will need a lot more information before we can being to guide you to a solution. We are not doctors so there are many things we do not know about insulin and how it is used within the body. What is the structure of the table(s) that store the insulin information? What is the half life formula you are using? Is there a point after which a shot of insulin becomes degraded so much that a particular shot of insulin no longer affects the amount of insulin within the body in any appreciable manner? Is insulin level and its half life calculated on a global level or on a per shot basis? Or does this not matter because of the characteristics of the half life formula? How do you account for flucutations in insulin usage? Especially around the time of a meal, either before, during, or after. What is this chart supposed to look like? Would it not be safer to take regular insulin level measurements rather than relying on a projection? Are there no pre-existing calculators that you can use that will have accounted for all this plus other questions that I may not have foreseen? Oct 28 '13 #2

 P: 5 Thanks for the replies. First off, I understand and appreciate your concerns regarding not being a doctor, and the potential for harm and for medical liability so let me be clear what is the scope and objective. Back Ground I'll start with a bit of type I diabetes background. It's an autoimmune disorder destroys the insulin secreting cells in the pancreas. In order to get blood sugar into the cells the body needs insuline. Blood sugar levels are monitored as blood glucose (BG) in mg/dl. There are many things that affect a persons BG beyond the scope but for our purposes let's just say food carbohydrates and fat along insulin injections are the largest factors. So, my son eats, sleeps, etc. and the insulin delivery and decay is calculated and managed by the pump with user input and tests are done as his blood sugar rises and falls during the day and night. As much as one would like, none of this is simply a plug in the number and watch it work. But all of this data from the results is available to mine and improve blood sugar management. Scope: The DB will store common foods eaten with nutritional content that's relative (input by user); nutrition amounts by meal will be calculated based on user defined meals, bolus times, types, amounts, etc.; blood sugar test results, standard and temp basals, pump settings will be manually entered or possibly read from an ASCII file output by the pump or meter. Objective: Current common practice is to keep records of carbs and bolus amounts. Our practice has been to keep records of typical meals and bolus types and amounts. My intent is simply to create a graphic display of data I already have to be able to look at results. I wanted that graphic representation of the data to include a curve of estimated active insulin amount. I also want to be able to look up "similar meals" based on carb and fat totals or ratios. I specifically do not plan on putting in any sort of "suggested bolus" based on previous results of similar meals or have the DB calculate any user inputs at all. My Problem/Question: I think I'm pretty close with the math and the basic access table stuff as I've done some Access DB applications. The issue is that I've always used macros and not code, at least not much code. This project will require me to use code to create a recordset of fill in the time that the chart covers. That is the part I don't know about. How to create and use that recordset for the chart to reference - along with other data stored already. Now - on to your questions (re-ordered) Q:What is the half life formula you are using? A:Per the Insulin manufacturer, the half life is 81 minutes. However, NIH also says Onset is in 10-20 minutes, peak is in 40-50 minutes and Duration of Action(DOA) is 3-5 hours. DOA is also a user controlled pump setting. It's been set at 4 hours. More later on the calculations. Q:Is there a point after which a shot of insulin becomes degraded so much that a particular shot of insulin no longer affects the amount of insulin within the body in any appreciable manner? A:Yes, this can be as simple as the the calculated life based on the half life and amount or the DOA time. Q:Is insulin level and its half life calculated on a global level or on a per shot basis? Or does this not matter because of the characteristics of the half life formula? A: I'm planning on a Global Level Q:How do you account for fluctuations in insulin usage? Especially around the time of a meal, either before, during, or after. A:My plan is to calculate the insulin rise and fall for each normal bolus using a straight line from 0 to peak at 40 minutes post bolus. The 40 minute peak will be estimated to be the amount of insulin at 40 minutes using the initial bolus and decay rate with half time of 81 minutes. Insulin amounts for t>40 will be calculated based on the decay alone. A bolus effective end time will be calculated. I will probably take basal amounts and temp basal amounts and although they will be stored differently, in the bolus summation procedure they will be calculated as additional small bolus amounts. Maybe one quarter the hourly rate given at 15 minute increments. One way I thought to do the bolus calculations is to select bolus records whose active times fall within the chart. Then create a matrix of the bolus contribution of each bolus starting at the earliest active bolus start time (even if the start is before the chart window) and ending at the end of the chart or the end of the latest bolus end time. Then divide the array into a number of minute increment(columns) to accumulate bolus amounts. The chart will then work off of this array Q:What is this chart supposed to look like? A: Based on start and end times it will show calculated active bolus insulin (normal and combo) with markers at bolus points, show calculated basal, carb intakes. Q:Would it not be safer to take regular insulin level measurements rather than relying on a projection? A:It's not possible to take insulin level measurements. My only "output" so to speak is blood sugar levels. Q:Are there no pre-existing calculators that you can use that will have accounted for all this plus other questions that I may not have foreseen? A:Not that I have found. Everything I have found is some basic graphing that will plot blood sugar tests, and note boluses but nothing that shows insulin amount. Also, I have found no diabetic management software that includes the meal information. You find meal software for people with nutrition and weight loss concerns or diabetes software that stores basic data, has some BG plotting, but no insulin calculations. Thanks again, Jetson Oct 29 '13 #4

 Expert Mod 10K+ P: 12,315 Let's start simple since this is such a large topic. Given a table that holds injection information. Injections Expand|Select|Wrap|Line Numbers InjectionTime              InjectionAmount 10/28/2013 7:00:00 AM      100 10/28/2013 9:00:00 AM      10 10/28/2013 11:00:00 AM     10 And given a table that holds a list of time intervals at which you want to see how much insulin is left. TimeTable Expand|Select|Wrap|Line Numbers TimeElapsed   40 81 120 243 And given this formula for half-life I found on wikipedia: `Insulin after t minutes have elapsed = (Insulin injected) * (1/2) ^ (t / 81)` Then all you need to find the amount of any shot at any time is this query: Expand|Select|Wrap|Line Numbers SELECT       Injections.InjectionTime,       Injections.InjectionAmount,       TimeTable.TimeElapsed,       IIf(DateAdd("n",[TimeElapsed],[Enter Report Start Date Time])>[InjectionTime],[InjectionAmount] * (0.5 ^ (DateDiff("n",[InjectionTime] ,DateAdd("n",[TimeElapsed],[Enter Report Start Date Time])) / 81)), 0) AS InsulinRemaining   FROM       Injections, TimeTable   WHERE       Injections.InjectionTime Between       DateAdd("h",-5,[Enter Report Start Date Time])       And DateAdd("n",243,[Enter Report Start Date Time])   ORDER BY       Injections.InjectionTime,       TimeTable.TimeElapsed; In the WHERE clause, I subtract 5 hours to include all shots given within the duration of action. And I project out 243 minutes to collect all shots given within the range of the time table. The results of this query, given a report time of 10/28/2013 10am, would be: Expand|Select|Wrap|Line Numbers InjectionTime     InjectionAmount     TimeElapsed     InsulinRemaining 10/28/2013 7:00:00 AM    100    0    21.4310995713268 10/28/2013 7:00:00 AM    100    40    15.219054283899 10/28/2013 7:00:00 AM    100    81    10.7155497856634 10/28/2013 7:00:00 AM    100    120    7.67492411506507 10/28/2013 7:00:00 AM    100    243    2.67888744641585 10/28/2013 9:00:00 AM    10    0    5.98432013073045 10/28/2013 9:00:00 AM    10    40    4.24969762371262 10/28/2013 9:00:00 AM    10    81    2.99216006536523 10/28/2013 9:00:00 AM    10    120    2.14310995713268 10/28/2013 9:00:00 AM    10    243    0.748040016341306 10/28/2013 11:00:00 AM    10    0    0 10/28/2013 11:00:00 AM    10    40    0 10/28/2013 11:00:00 AM    10    81    8.35516799030218 10/28/2013 11:00:00 AM    10    120    5.98432013073045 10/28/2013 11:00:00 AM    10    243    2.0887919975755 This detail can then be aggregated on the TimeElapsed and summing on InsulinRemaining to provide the data points for a chart report in Access. Start with this as a base, validate the results, and build outwards from there. Oct 29 '13 #5

 P: 5 Thanks Rabbit, that's a great start!! I am a bit confused on the report time, time elapsed and the query output because at 11:00 and time elapsed = 10 and 40 insulin remaining is 0. Can you help me with the syntax if I want to SELECT INTO a temp table. Either creating the blank table and then putting the records into it, or using Injections to created the table, then append the similar records created by the same routine applied to the Basals. Thanks Again! Oct 29 '13 #6

 Expert Mod 10K+ P: 12,315 The shot happens at 11 am. 40 minutes from the report date of 10 am is 10:40 am. The shot has not happened yet. There's no need to create a table from the query. You can report directly off the query. Oct 30 '13 #7

 P: 5 Ok, I see. As is it clips the curve by missing the start but I can just add in more time steps to minimize that issue. Thanks. Oct 30 '13 #8

 Expert Mod 10K+ P: 12,315 Yes you can, you can add in as many time steps as needed. They can all be one minute apart for 5 hours if you wanted. That would give the chart 300 plots to point. Don't forget to create an aggregate query first and use that for the chart, not the detail. The detail is there mostly for you to validate the data. Remember that this is just a starting point. There are a lot of caveats you mentioned in your other posts that are not handled by this. You should get comfortable with the basic model before building in everything else. Oct 30 '13 #9

 P: 5 Yes but this puts me well on my way. I already have the other tables and forms pretty much worked out but not beautified. I have the foods, and portions and total meal nutrition working. I have the normal and combo bolus calculations and forms working. I think what I will do is create a hidden table (injections) that I will put the combo bolus and basal rates into as small injections evenly spaced during the hour over the duration. Then the graph can work off of a single query. That math and functionality needs to be tested but it's off to a good start. Oct 30 '13 #10

 Expert Mod 10K+ P: 12,315 Let us know if you run into any problems. If you have any new questions, please create a new thread for it so we can keep each question separate. Oct 30 '13 #11