By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,064 Members | 1,388 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,064 IT Pros & Developers. It's quick & easy.

Crosstab query for grouping time

100+
P: 171
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
Dec 15 '13 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,397
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)
Dec 15 '13 #2

Expert 100+
P: 1,221
rajeevs,
You could find the minute number of the day
Expand|Select|Wrap|Line Numbers
  1. (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
Dec 15 '13 #3

100+
P: 171
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM 
  2. Count(Left([TimeField],2)) AS [The Value]
  3. SELECT [Tbl].DATE1 AS [DATEFld]
  4. FROM [Tbl]
  5. GROUP BY [Tbl].DATE1
  6. PIVOT Left([TimeField],2) 
  7.     & "00-" & Left([TimeField],2)
  8.     & "59";
Dec 15 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.