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.
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:)
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.
Thank you. It worked like a charm using the timestamps.
Thank you!! haha. It worked like a charm using the timestamps.
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*
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.
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. ???
This is what I have so far: