9 Replies Latest reply on Feb 24, 2015 11:14 AM by KimB

    Calculate response times for events that occur during certain events

    KimB

      Title

      Calculate response times for events that occur during certain events

      Post

      I have 2 tables: CFS and IVC.  I am trying to determine response times for each call in CFS that occurs within a time frame of each call in IVC.  The times are set up as timestamps (mm/dd/yyyy hh:mm:ss - CFS....but mm/dd/yyyy hh:mm - IVC).  Then, I need to calculate the average response times during those specific time frames of each IVC.

      First, I have no idea how to even relate the 2 tables because this is basically a temporal comparison between the 2 tables.  These are the fields:

                                                                                                                                                                                                                                         
      IVC
      call_numberivc_serv_date_timecomp_date_time
       STARTEND
         
                                                                                                                                                                                                   
      CFS
      call_numbertime_disptime_onsresptime_disp2ons
       STARTEND                

      =END-START

                     

      Second, although both tables have the same field, call_number, not every call_number in IVC is in CFS. 

      For some reason, I just cannot wrap my head around this.  I am familiar with MS Access but own a Mac, so I got FM Pro Adv 13...and here I am.

      Advice? Tips? Hold my hand and walk me through it? :)  Thanks in advance!

        • 1. Re: Calculate response times for events that occur during certain events
          philmodjunk

          Figuring this out would appear to be step one:

          First, I have no idea how to even relate the 2 tables because this is basically a temporal comparison between the 2 tables.

          I don't know nearly enough about what you are doing to have any confidence that a suggestion at this point will actually work for you. From what you post, it looks like you can link the two tables by call number, but I do not know if "call number" is a field that uniquely identifies each record in one or the other table or is a phone number. I am making assumptions here to even say Phone Number as you don't really make clear whether a "call" is a phone call or some other type of "call". 

          This phrase in particular seems confusing and raises a lot of doubt in my mind that I am correctly reading "call" as a "phone call":

          I am trying to determine response times for each call in CFS that occurs within a time frame of each call in IVC

          Making one Phone call within a "time frame" of another phone call doesn't really make sense to me and hence I wonder what you mean by "call" here.

          You may need to post a narrative description of what your users are doing here when they are are recording data into these two tables.

          PS: I have also designed Access databases and have at least a rudimentary knowledge of SQL, so if you need to describe how you would do it in Access in order to better describe your issue, feel free to do so.

          • 2. Re: Calculate response times for events that occur during certain events
            KimB

            Phil, thank you for your response and willingness to help.

            The call_number is actually a specific, unique identifier for calls that come into a 911 center.  This db is for personal research, only my use, for a work-related project.  I am the only one who will have access to and will utilize the db.

            I need to see the response times for calls in CFS when we have a specific type of event occurring in IVC, ivc_call.  So, there will be some call_numbers in IVC that will be in CFS, but not vice versa.  I am trying to compare response times  (RT) to calls during the ivc_call and at other times when there is no ivc_call.

            Also, the date/time for the calls is formatted as a timestamp (mm/dd/yyyy hh:mm:ss) in CFS.  But in IVC it is formatted as (mm/dd/yyyy hh:mm) without the seconds. (I do not know if that will matter but figured I would mention it.)

            I hope this helps you better understand my predicament.  For some reason, I just cannot wrap my head around it.  I do not have access to Access (excuse the redundancy) since I have a Mac, so it would be really tasking for me to remember utilizing this software over a decade ago:)

            • 3. Re: Calculate response times for events that occur during certain events
              schamblee

              I believe your relationship needs to be based on the timestamps because that is the data that relates the tables.   This type calculation / database can get complicated.  I created a sample database but I didn't get into testing with times and dates that cross over midnight to the next date.  Maybe this sample will get you going in the correct direction.  I'm better at creating samples than I am at explaining.

              https://www.dropbox.com/s/fhogjkuydlsj6v4/SampleResponseTimeDB.fmp12?dl=0

              • 4. Re: Calculate response times for events that occur during certain events
                KimB

                Phil,

                Thank you. It worked like a charm using the timestamps.

                • 5. Re: Calculate response times for events that occur during certain events
                  philmodjunk

                  Then you should thank S Chamblee for his generous assistance rather than me. wink

                  • 6. Re: Calculate response times for events that occur during certain events
                    KimB

                    Chamblee!!

                    Thank you!! haha.  It worked like a charm using the timestamps.

                     

                    Next obstacle....

                    I am trying to average the response times in a sub-summary field with the count of all complaints from CFS that occur during an incident in IVC.  I am assuming that I will have to use the expression: dispatched >= ivc_served and dispatched <= ivc_comp  but I'm not sure where to use it.  The average function does not work for what I need to do.  It will produce the 1st response time for the 1st complaint within that range.  I tried the If function.  Did not work.  Produced a ?.  *banging my head*

                    • 7. Re: Calculate response times for events that occur during certain events
                      schamblee

                      You may have to redesign your database.  My sample was a one to many relationship and I believe you are going to need a many to many relationship, so you going to need a join table between the cfs table and the ivc table. This join table will contain a timestamp that you are computing the average response time.

                      • 8. Re: Calculate response times for events that occur during certain events
                        KimB

                        Chamblee, I'm a visual learner. Can you post a screenshot of a similar example?  That would truly help! ...I was also raised on MS Access, so I'm having difficulty converting to FM Pro.

                        I created my join table with the fields from both tables that I want to link.  Then, I created the avg_RT field with the calculation but none of it is now working in my report.  Maybe  I just need to sleep on it. ???

                        • 9. Re: Calculate response times for events that occur during certain events
                          KimB

                          This is what I have so far: