..do you mean returned within 24 hours or one the same day as the message was left?
Also if the call was returned in one day she wants it broken down into minutes (as in how many hours and mins was the call returned)
do you need the detail of each call/message or do you need a sumary
lets assume you want the detail
as its in a report you don't actually need to do anything special or clever within the query, providing you have the columns you need for your report. you casn do all of thsi within a report using soem code behind specific events in an Acces report. IF however you think the same essential data is required forsay more than one report and or form then doing it in a query makes more sense. remember the adagae of write once, run many times....
presuming that if a message is only returned during working hours AND if the message is returned on a different day to the day it was left then you need to allocate time accordingly.
lets also assume you are going to do this within the report NOT a query
so lay out your report as you would normally.
for now we are dealing with the reports detail
add a hidden text box control to store
the elapsed number of minutes, its datasource will be =iif( is NOT NULL(datereturned), datereutrned-datetaken,0)
the elapsed number of working minutes. to calculate that you will need to call a function, called say CalcElapsedWorkingMinutes, its datasource will be = iif( is NOT NULL(datereturned), CalcElapsedWorkingMinutes(datetaken, datereutrned), 0 )
OK so thats your time difference between the two events sorted. the IIF handles the case where the datereturned is null and if so sets the difference to 0. NOTE if you are going to do averages on this data then set it to NULL instead. this will allow the avg function to work correctly
so in our two hidden text boxes you have the calendar elapsed and the working time elapsed difference in minutes. next you need to format that, again depends on how flexible you think this needs to be but I'd probably push this into a function
Id probably add another hidden column which identifies the number of messages returned, called say NoRetruned, and set its datasource to =iif(is not null(datereturned),1,0)
in a footer doing a COUNT on this control gives you the number of messages left, a SUM gives you the number of messages returned and therefere you can derive the number of messages not returned
then in your group footer (if required, bear in mind a group footer can be a page and or a report footer
Im guessing you'd want
the number of calls left
... thats the COUNT of any control in your detail
the number of calls returend
... thats the SUM of the control No Returned
the average interval
...thats going to be
..or do it the hardocre way (SUM(elapsedTime)/count(NoReturnedCalls))*100
I'll let you develop the functions . there are so many design decisions
what to do if you have data out of range, what is a suitable default/return value if out of range. if it were me I'd set a default start time to be 06:00, a default end time to be 16:30, anything with invalid data gets those defaults on the grounds that it swill screw with their numbers and make people more attentive tot he data capture
I'd rather be riding on the Tiger 800 or the Norton