Crosstab query for grouping time

[rajeevs]

Hi
I have a crosstab query which has column heading as Expr:Left([Time Field],2) & "00-" & Left([Time Field],2) & "59" which shows my columns as 0000-0059,0100-0159,0200-0259 etc upto 2300-2359.
The value field is as The Value:Count(Left([Time Field],2))which gives the count of records falling in that time range.
I need a help to do the column heading break up as
0030-0129, 0130-0229, 0230-0329, 0330-0429 etc and find the record count between that time range.
I forgot to mention that the Time Field is a time in hh:nn format.
I thank you all the experts for the help in advance

Regards

[zmbd]

Show us the SQL you are currently using.
Please remember to select it and format using the [CODE/] button.

(open Q.Editor in design, then switch to SQL by right clicking in a blank area of the table display)

[jimatqsi]

rajeevs,
You could find the minute number of the day

(Hour([TimeField]) *60)+Minute([TimeField]))

and divide it by 90, rounding up to whole numbers. That will give you a number between 1 and 16, which can be used to derive your column titles as you like.

However, what about the period 00:00 to 00:30. Did you mean to exclude that? That complicates things a bit because the day no longer consists of whole 90-minute periods.

Jim

[rajeevs]

The Sql is as below which gives me column headings as 0000-0059, 0100-0159,0200-0259 etc and count of records between 0000-0059, 0100-0159, 0200-0259 these timings:

TRANSFORM 
Count(Left([TimeField],2)) AS [The Value]
SELECT [Tbl].DATE1 AS [DATEFld]
FROM [Tbl]
GROUP BY [Tbl].DATE1
PIVOT Left([TimeField],2) 
    & "00-" & Left([TimeField],2)
    & "59";