7 Replies Latest reply on Jun 23, 2012 1:25 PM by disabled_JackRodgers

    Trying to get average of all Records in a Database I'm keeping for Diabetes.

    pjonesCET

      Title

      Trying to get average of all Records in a Database I'm keeping for Diabetes.

      Post

      I have database for  Glucose  reading see screenshot:

      http://www.screencast.com/t/UftdvzuBA0

      In this A1C is devided by days which is sum of Day a Serial Number.

      Actually there are 3 entries for each

       

      So Days should actually be 35.67  into the so the totals of all the entries (Breakfast Reading; Lunch Reading: Dinner Read) should be devided by 35.67 in this case)

      A1C equal  average of all your meter readings over a 90 day period

       

      I can upload the aqual file to DropBox, Sugar Sunc or even MS SkyDrive.

        • 1. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
          pjonesCET

          Here are links to two version of the file (layout different)  uploaded to SkyDrive:

          http://sdrv.ms/L9QK2x

          and:

          http://sdrv.ms/O5zG4r

          • 2. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
            philmodjunk

            I'm not sure I can tell what you want here.

            A1C is the value you want to average...

            All A1C values in your table, one value to each record?

            If so, use either the Average summary field After Show All Records or use the Average ( ) function from a related table that matches to all records in your table.

            A moving average of the most recent 90 days of readings? 1 reading per day?

            Same method works, but you perform a find (summary field ) or set up a relationship that matches to only records in that date range (sum function).

            Or do you have multiple readings per day?

            In that case, do you want an average for the day,  an average of the daily average over a date range....

            And if you have multiple readings per day--as I think diabetics would have, Do you have one reading per record or multiple readings recorded in the same record?

            • 3. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
              pjonesCET

              Each day has three readings for a total of 270 readings in a 90 day period.

              In the example given. I hadn't completed the reading for dinner so the day is .67 day (2 devided by 3).  So total days would be 35.67.at the point this file was worked on.  There is a total of 107 entries. (The time is of no interest.) for figuring

              The AIC would be all the Glucose readings reading averaged divided by the number of days.

              In other words it needs to use "all" the gloucose Fields.

              In each record there is a Daily average  which is the average(Breakfast Reading;Lunch Reading;Dinner Reading) in each record but that's strictly to see how I am doing for the day.

              If you want to down load either one of the files I have put on skydrive publick links feel free to do so I don't mind anyone seeing the readings.

              Think I got it figured it out  I created 4 addition fields that are not printed.

              Entries   Summary = Count Breafast reading (running)
              Entries2 Summary = Count Lunch reading (running)
              Entries3 Summary = Count Dinner Running (running)
              Total Entries Calculation = (Entries; Entries2; Entries3)

              Days  is changed to Calculation = Total entries / 3

              Days will now show up as fractions of days  and that will divide into averaged reading of all the readings.

              • 4. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
                GuyStevens

                Hey PJones,

                I just downloaded your file and had a look.

                I haven't really looked at the averages yet, and you say in your previous post that you have them figured out.

                But I noticed another problem.

                You seem to be doing your data entry in your print layout.

                If you want you can make another layout for your data entry and use the layout you have now for printing.

                On my monitor this layout looks extremely small, and hard to work with.

                Also, I notice you use your day field as an auto enter ID field. So you need to be carefull never to delete a records or you are busted.

                A better way might be to create a seperate ID field with an auto enter serial number and make the day field an auto enter calculation of Max day + 1.

                Also, what about after your first 90 day period? Do you then need to continue with another set of readings?

                And ho would you go about that? Would you make a new file?

                Isn't it better to keep it all in one file, so you can always have a look back at older data?

                • 5. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
                  Sorbsbuster

                  Why not have a Summary (average) field of the DailyAverage field?

                  • 6. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.
                    GuyStevens

                    In terms of using multiple layouts I made you a little example:

                    http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/pjonesCET%20-%20Phillip%20-%20Glucose%20average/Glucose2_DaSaint.fmp12

                    I'm not sure about the averages however.

                    Is the A1C per day or per reading? (day *3)

                    Check it out ad let me know what you think.

                     

                    • 7. Re: Trying to get average of all Records in a Database I'm keeping for Diabetes.

                      A second idea is to create a calculated field (and I am simplifying here) that picks up the value IF the date of entry is within the last 90 days. Then you can summarize on this field without any complicated this or that. (My prefered method...)

                      CalcField for Value

                      Case(

                      date < currentdate - 90 ; "" ;

                      readingdata

                      )

                      Now create your average summary field on this calc field and hopefully it works!

                      I have not tested this but I have high hopes for it...  You may have to set the calc field to recalc...

                      Also it helps if your readings are each a new record in a table rather than three fields in one record...