Like a pivot table, but not

[omahatom]

Can anyone offer any thoughts on how to solve a problem...I have a table with individual phone calls (one per row) and the time they each arrive by minute (an attribute on the same row). I need to show for every minute of a day (1,440 rows) a count of how many calls arrived. I've tried various cross-tabs, pivot tables and inner join queries, but those processes don't show minutes for which there are no phone calls...and I need to show all minutes.

I've resorted to listing each phone call in excel in individual columns with all 1,440 minutes of the day on the rows with a "1" on the appropriate minute. Then, I have a total column that sums across all columns and then shows for each row (each minute) the total number of calls.

There HAS to be an easier way!?!!?

Any thoughts would be appreciated!!

 

[namliam]

for a table of every minute in every hour (of every day?)

All you really need is a table for sake of simplicity contains 60 records 0 thru 59
Lets call that table tblMinutes
Make a query, lets call that qryHours
Select Minutes as hours from tblMinutes where minutes < 24

Now the magic happens
make another query, a "cartegian product"
SELECT TimeSerial([hours],[minutes],0) AS mytimes FROM tblMinutes, qryHours

Simply left outer join this to your original query
I hope this helps?

fyi, it can also (easily) be done with a few more queries and only i.e. 10 records