MS Access Reporting for an Interval

February 12, 2007 12:29 PM

Microsoft Access: How to query data with a variable that is a time value, and incremented.

I have a bunch of data (specifically call logs) that I would like to create a trunk usage report from. The data is simply dropped into Access for each outbound/inbound call (TrunkDirection = 2, or 3).

So I have this form (sorry if my SQL is horrible):

SELECT count(*) from Connect
WHERE TrunkDirection = '2' // run again with 3 here
AND PortNumber != '0' // 0 means internal call
AND ConnectTime < $time and disconnecttime> $time
GROUP BY SwitchID // Meaning 'site'

I would like $time to be 0:00:00, 1/1/07, and increment every 60 seconds to create a report that lists my 5 sites (5 switch ID's) and a count of the calls that are occuring at that minute interval.

My concern is how to implement the $time in Access. I don't know VBS, and could do this with Perl/PHP, but would rather not due that. Externally I would simply run 89280 queries against the data, and create a new 89280 table to report on.

(89280 = 60 minutes * 24 hours * 31 days * 2 call states)

posted by SirStan to computers & internet (7 comments total)

I'm not exactly sure I understand your situation correctly, but I'll take a shot. It seems you could create a list of all the times easily in Excel (A1 = zero time, A2 = A1 + increment, drag A2 formula down). You'd have to use two sheets since you're over 65500 times. Import the sheets as tables, insert one into the other so there's just one time table with all your times. Then add your time table to your existing query ("from Connect, timetable") without joining it, and replace "$time" with the name of the field in the time table. Good luck.
posted by erikgrande at 2:05 PM on February 12, 2007

erik: I am looking for something more automated. This is the call data for over 200 employees. The call log is pretty huge. I am looking for an automated way to collect trunk usage percentages for trend analysis.

Excel+Access wont work. I either want it all inside Access (simlpy due to the number of queries), or with PHP/Perl+Access if I need to go external (in which case I would probably drop the results into MSDE/MySQL/Postgres based on performance.

Thanks.
posted by SirStan at 3:03 PM on February 12, 2007

Not sure if I'm following, but maybe this would work . . . ?

With the Format function, you can return the ConnectTime field as a string that contains just the minutes part of the time (e.g., excluding the date and the seconds), and group by it.
posted by treepour at 3:59 PM on February 12, 2007

(On second thought, not sure the Format function alone will do the trick, but it could at least help you get the date into a string that you could manipulate further with string functions).
posted by treepour at 4:01 PM on February 12, 2007

not got time to syntax it right now , but i would do this (in principle) in Oracle., :S

build an outer loop that increments a varible and pass that variable through to the sql ...
loop
select ...
connect_time > '00:00:00 1/1/07'+(x) etc ;

return '00:00:00 1/1/07'+(x) , Count(*) ;
(x):=(x)+(onesecond) ;
end loop
sorry i cant be more helpful with the access specifics , but its defo a loop ur looking for , hope this is at least some help ..
posted by burr1545 at 3:28 AM on February 13, 2007

treepour pretty much has it.

1. Write a view that turns connect_time and disconnect time into just time* (throw away the date part), and adds separate columns for the date of each. (See my answer to this askmefi for details.) I'll call this view connect_minutes.
2. Add the table and views to produce an artificial_range of type time*, as explained in my answer to this askmefi. Make sure your range produces a full day's minutes, or 60*24 rows. I'll call this view a_r.
3. join a_r with connect_minutes, and group by the data of interest:

SELECT b.connect_date, a.whole_minute, count(*), b.switch_id, b.trunkdirection, b.portnumber
from a_r a, connect_minutes b
where a.whole_minute < b.disconnect_time and a.whole_minute >= b.connect_time
group by b.connect_date, a.whole_minute, b.switch_id, b.trunkdirection, b.portnumber
order by b.connect_date, a.whole_minute, b.switch_id, b.trunkdirection, b.portnumber

Obviously, you should order the column_list in the group by and order by as best suits your needs.

That will give you all your data, for all minutes in all days, for all switches, both trunk directions, and all port numbers, in one SQL select statement. Note that it doesn't handle wrap-around, where a call starts on one day, goes on through midnight(s) and ends on a different day; the solution for that is left as an exercise for the reader.

* if your database doesn't have a pure time type, use a datetime or timestamp type with the date-part set to zero, e.g., '0000-00-00 01:00:00'.
posted by orthogonality at 4:17 AM on February 14, 2007

(My solution above won't necessarily give all days; days not in connect table's connecttime won't be listed. If calls don't occur on all days but you want to see all times for all days listed anyway (e.g., for output to a plot/graph), you can fix that by joining to another artificial_range view, a view of dates rather than times as in the original question that prompted my artificial_range answer.)
posted by orthogonality at 4:32 AM on February 14, 2007