Creating a chart from accumlated datasets.

[Jetson]

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.

[Rabbit]

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?

[zmbd]

Jetson:
I know that I for one would like to help you with this, along with I'm sure many others here. Such a database could be really helpful for many people; however, there is a liability issue here that has to be cleared up:

It must be made very, very clear… no one here can provide any medical advice or guidance in any manner whatsoever nor can we, nor will we, provide any guarantee that what is developed will meet your or your son’s medical needs. The use of any information provided is at your own risk and you should make sure to review any of the results you are obtaining with a trained medical professional that is knowledgeable about you and your son’s medical needs – in addition to just plain old common sense review of the returned results.

One of my professors was diabetic and there was a chart that he used to 'plot' this information based on time, blood sugar results, and insulin dosage... you might check with your doctor. This chart would be really old-school stuff. Unfortunately, my prof passed away over a decade ago so I can't contact him for the name of the chart. I don't think Access can make such an XYZ chart either.

[Jetson]

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(colum ns) 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

[Rabbit]

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

Given a table that holds injection information.
Injections

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

TimeElapsed
0
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:

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:

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 InsulinRemainin g 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.

[Jetson]

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!

[Rabbit]

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.

[Jetson]

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.

[Rabbit]

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.

[Jetson]

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.

[Rabbit]

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.