1 2 Previous Next 15 Replies Latest reply on Jul 25, 2012 4:08 AM by matyson

    Running Totals

    matyson

      Dear Forum,

       

      I have created a database in which data is collected for a student that comes to see the nurse. The database contains many fields in which we document anything that is wrong or anything we do with this specific student encounter. Each record records one student encounter. An example of an encounter may be:

      Student name:John Doe, Nurse:Mary, Pain:X, Action:Bandaid, Parent Call:X

      Student name:John Doe, Nurse:Mary, Pain:_, Action:Bandaid, Parent Call:X

      Student Name: Jan Doe, Nurse:Julia, Pain:X, Skin:Insect Bite Action:Cream applied, Parent Call:X

       

      Now I have to tabulate data for this encounter.

      Want to count: No. of encounters in a month like the above for each nurse - Mary 2, Julia 1

      Want to count: No. of pain encounters per nurse - Mary 1, Julia 1

      Want to count: No. of parent calls per nurse - Mary 1, Julia 1

      And so on.

       

      I am at a lost as to how to start the process. Can someone please help?

       

      Mary Tyson

        • 1. Re: Running Totals
          erolst

          Mary -

           

          please check your private messages.

          • 2. Re: Running Totals
            ryan

            was this answered?  if not, please let us know what relationships and tables already exist in your DB.

            • 3. Re: Running Totals
              matyson

              Not quite.  I needed the report to show all of the totals on one report.  The place where we put the dates is going to work great.  I will send you the layout of the report without the fields being formed and the relationships of my tables.  The database is on a secure server only accessible from work, so I will get this to you in the next day.

              • 4. Re: Running Totals
                matyson

                Thank you erolst for the database example...unfortunately it is not quite what I need.  I love the part that allowed you to put in the dates and choose the type of report.  I think that creating summary reports for upto 5 nurses and a master summary choice would be more in line with what I need.  Each month a nurse must submit are report with just the total numbers to the county during the school year and then add these numbers up at the end of the year for a report to the state.  This is what we use the report for.

                 

                I thought giving you just a little background would help.

                 

                Ok...I have tried to quickly create a sample database (here is the link:https://www.dropbox.com/sh/p3efyuef8d8pjaq/j2QN5Mi4iM).  This is a link you should be able to connect to without an account with dropbox.

                 

                On this link is database one, SME Test which has the layout that we will use to input the data.  Unfortunately because the database is saved on a secure server without access from outside of school and a copy of the actual database cannot be made, I have only the layout without the actual fields or value lists attacted.

                 

                The second database in the directory I want to create that will give me the actual numbers tabulated from the first and this is the report I am trying to complete.  I gave just a very brief example, but we are tabulating so many numbers.

                 

                The third databse is the one erost created.

                 

                I need a summary report for each nurse of the different things we tabulate.  My email address is mary.tyson@macon.k12.nc.us for direct communication and transfering of files to me.

                 

                I really appreciate any help on this.  If you could just help me with the initial structure and format just one of the edit fields and one format for a checkbox field that could have multiple entries, I can replicate this in the database. 

                 

                Thanks much, Mary

                • 5. Re: Running Totals
                  comment

                  This is very confusing: first, why not attach the files here - and indicate which is which?

                   

                   

                  matyson wrote:

                   

                  Each month a nurse must submit are report with just the total numbers to the county during the school year and then add these numbers up at the end of the year for a report to the state. 

                   

                  Is it correct to assume that for each nurse, the total of specific encounters may exceed the grand total? For example:

                   

                  Mary:

                  • Total number of encounters: 1

                  • Number of pain encounters: 1

                  • Number of parent calls: 1

                   

                   

                   

                  Also, is this report submitted for each nurse individually or all of them together?

                  • 6. Re: Running Totals
                    Bobster

                    Disappointing to see FMI has 'blown it' in Seth Godin's opinion:

                     

                    http://sethgodin.typepad.com/seths_blog/2012/07/commander-obvious-chimes-in-with-tip-3-for-effective-web-marketing.html?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3Atypepad%2Fsethsmainblog%28Seth%27s+Blog%29

                     

                     

                    Bob Stuart

                    Lord Of The Files

                    Think Data Pty Ltd

                    Noosa

                    Queensland

                    Australia.

                     

                    +61 7 5442 5624

                    +61 413 350 993

                    • 7. Re: Running Totals
                      comment

                      Don't see the relevance.

                      • 8. Re: Running Totals
                        matyson

                        Michael,

                         

                        Yes it it true.  We have combined all the numbers we must report for different reports into one.  Each number stands on its own for the nurse and the grand totals would be adding a total for each category.  For example:

                         

                        Mary had 30 monthly encounters, 10 with pain, and 15 parent calls.

                        Jane had 40 monthly encounters, 10 with pain, and 25 parent calls.

                         

                        Totals 70 monthly encounters, 20 pain, 40 parent calls.

                         

                        We would not add all of Mary's total together.

                         

                        I hope this helps clarify.  Mary

                         

                        Mary Tyson, BSN, RN, NCSN

                        School Nurse

                        Macon County Public Health

                        1830 Lakeside Drive

                        Franklin, NC 28734

                        (828)421-3506

                        http://www.maconnc.org

                         

                        Pursuant to North Carolina General Statues Chapter 132, Public Records, this electronic mail message and any attachments hereto, as well as any electronic mail messages that may be sent in response to it may be considered public record.  Also, any information contained in this message that may be considered “Confidential” will be withheld from any public record requests.  If this e-mail contains protected health information or personal identifying information, you are hereby notified that any further dissemination and/or distribution of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by replying to this message and deleting it from your computer.  Thank you.

                        • 9. Re: Running Totals
                          matyson

                          I am sorry that I didn't attach the files here…my first email with an attachment stated that he couldn't attach messages with private messages.  I didn't even try.  Here are all of the files. 

                           

                          My attempt at creating a database to hold the numbers from SME test.  This database has all of the different categories I must tabulate as a separate field.  It also has the layout I thought may be easiest to store the data.

                          Database created by FileMaker team member to first address the issue.  I like the configure reports section of this database, but the totals report lists each record…because of privacy issues, we only want the total number by nurse to be put on the report.

                          This database has the layout of how we input the encounters….but because the actual database is protected from coping, the fields are not setup.

                           

                          I hope this helps with the confusion.  I only need a head start on the process or someone to explain how to configure the field calculations or scripts to sum the information.

                           

                             

                          Mary Tyson, BSN, RN, NCSN

                          School Nurse

                          Macon County Public Health

                          1830 Lakeside Drive

                          Franklin, NC 28734

                          (828)421-3506

                          http://www.maconnc.org

                           

                          Pursuant to North Carolina General Statues Chapter 132, Public Records, this electronic mail message and any attachments hereto, as well as any electronic mail messages that may be sent in response to it may be considered public record.  Also, any information contained in this message that may be considered “Confidential” will be withheld from any public record requests.  If this e-mail contains protected health information or personal identifying information, you are hereby notified that any further dissemination and/or distribution of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by replying to this message and deleting it from your computer.  Thank you.

                          • 10. Re: Running Totals
                            comment

                            You haven't answered my second question.

                            • 11. Re: Running Totals
                              matyson

                              All of the nurses together...sorry I didn't see the second question.  Mary

                              • 12. Re: Running Totals
                                erolst

                                Maybe this is better suited to your needs; the data model had to be tweaked a bit to make this kind of report work, i.e. the individual incident types had to go into their own table.

                                 

                                In order to anonymize the report, there is no body section, simply one grand summary (all encounters in the period) and the breakdowns for the individual nurse per incident type.

                                Note that the report configuration script uses the hard-coded IncidentTypeIDs for parent call and pain to find the correct records; obviously the report configuration could get a UI which allows for the selection of the desired types, in case your reporting requirements change.

                                 

                                btw, I'm no “FileMaker team member” … and you can attach files to regular posts, just not to PMs, AFAIK.

                                • 13. Re: Running Totals
                                  comment

                                  matyson wrote:

                                   

                                  All of the nurses together...

                                   

                                  Well, then I am afraid this is not going to be as simple as it may seem at first. Because the reality is that you want to count up more things that you actually have. So the straightforward method of sub-summaries and grand summary will not work here - because the same record cannot be counted in two different sub-summaries.*

                                   

                                  Now, the simplest method - even if somewhat tedious - to overcome this difficulty would be to create a separate summary field for each incident type. For example, if you have a field (in the Encounters table) to describe pain (or to indicate the presence of pain), then a summary field counting this field would give you the Pain column in your report.

                                   

                                  There are smarter methods that could be employed, using less resources. For example, you could compile your report using a script to count each category in turn (within the given date range) separately and store it in a variable, then using a calculation field to display the collected stats in a tabular form. However, this may require some pretty fancy scripting.

                                   

                                  ---

                                  (*) erolst's latest file attempts to fix this issue by creating a "grandchild" record for each incident counted. However, it's a big change that requires a lot of auxiliary measures (e.g. a mechanism to delete the related record, when it turns out that "pain" was marked by mistake). My own tendency is not subordinate my data model to external reporting requirements - unless that is the only reason why the solution exists in the first place.

                                  • 14. Re: Running Totals
                                    matyson

                                    Thank you so much, This actual might work.  I just need to add in all of the different types to expand the report.  Again thank you.

                                     

                                    Mary

                                    Mary Tyson, BSN, RN, NCSN

                                    School Nurse

                                    Macon County Public Health

                                    1830 Lakeside Drive

                                    Franklin, NC 28734

                                    (828)421-3506

                                    http://www.maconnc.org

                                     

                                    Pursuant to North Carolina General Statues Chapter 132, Public Records, this electronic mail message and any attachments hereto, as well as any electronic mail messages that may be sent in response to it may be considered public record.  Also, any information contained in this message that may be considered “Confidential” will be withheld from any public record requests.  If this e-mail contains protected health information or personal identifying information, you are hereby notified that any further dissemination and/or distribution of this communication is strictly prohibited.  If you have received this communication in error, please notify the sender immediately by replying to this message and deleting it from your computer.  Thank you.

                                    1 2 Previous Next