Crosstab query with time range as column heading

Hi All,

I have a table for logging phone calls that records the time of each record, looking something like this:

CallID--|-- Time--|-- Date --|--Call Reason
1 -----|--10:23--|-10/1/09--|- blah
2 -----|--11:45--|-10/1/09--|- blah
3 -----|--14:53--|-11/1/09--|- blah

I want to create a query that will count the number of calls per hour on each day. Something like:

Date----|-9-9.59-|-10-10.59-|-11-11.59---- etc.
10/1/09-|--- 2----|--- 3-----|--- 1------
11/1/09-|--- 1----|--- 4-----|--- 5------

Where the values 2, 3, etc. are a count of CallID for that date and time range.

Using the cross-tab query wizard, if you set 'Time' as the column headings, you can then group the columns, but only by things like month or quarter. That produces a query like the below:

TRANSFORM Count(Table2.id) AS CountOfid
SELECT Table2.Date, Count(Table2.id) AS [Total Of id]
FROM Table2
GROUP BY Table2.Date
PIVOT Format([Time],"mmm") In "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I'm guessing it's the last line (PIVOT) that I need to worry about.

The red 'mmm' indicates the three digit month format, the blue indicates the column headings, but what to change them to?

I'm no Access/SQL expert - my sum knowledge comes from looking at the SQL and taking a guess at what it means, so any help would be great!

Thanks in advance, H.

First create a new query with all your fields in it. Then add a new column that looks like this.

Hr:left([CallTime],2) & left([CallTime],2) & ":59"
So a call time of 10:30 will be translated to 10 - 10:59

IMO it would look better being 10-11, 11-12, 12-13, etc

which would be
Hr:left([CallTime],2) & " - " & Val([CallTime],2) + 1


Then do a crosstab query using this new column for your column headings grouped by date. This should give the desired results.


To ensure that all hourly intervals are displayed on the report, even if there is no activity for that period you will need to go to the column properties and type in the column headings manually there, such as:

"08 - 09","09 - 10","10 - 11", etc

Thanks D,

Before you responded, I had worked out another way to do it, although it's far less elegant than your solution!

PIVOT IIf([time] Between 0.333 and 0.3749, "8-9 am",
iif([time] between 0.375 And 0.4169,"9-10 am",
IIf([time] Between 0.417 And 0.4579,"10-11 am",
IIf([time] Between 0.458 And 0.4999,"11am-12pm",
IIf([time] Between 0.5 And 0.5419,"12-1 pm",
IIf([time] Between 0.542 And 0.5839,"1-2 pm",
IIf([time] Between 0.584 And 0.6249,"2-3 pm",
IIf([time] Between 0.625 And 0.6669,"3-4 pm",
IIf([time] Between 0.667 And 0.7089,"4-5 pm",
IIf([time] Between 0.709 And 0.7509,"5-6 pm"))))))))));

Where the range of decimal numbers equals the second part of the double precision number for the date/time.

I then had to reformat the time field in the table (=Format(Now(),"hh:mm")) so that the time was recorded as only the time portion, and not the full date/time.

Not a very pretty bit of SQL, but the same result.

Thanks for your response.