I end up getting
The Hour Thecount
0 1333
I am sorry that I did not mention that Time1 is a date/time value based on a
24 hour clock.
Working on it last night I came up with this
TRANSFORM Count([Crime Table].[UF61#]) AS [CountOfUF61#]
SELECT Int([Time1]*24) AS thehour, Count([Crime Table].Time1) AS
CountOfTime1
FROM [Crime Table]
GROUP BY Int([Time1]*24)
PIVOT [Crime Table].Type_Crime;
that yields
thehour CountofTime Burg grand larceny GLA
0 14 4 4
6
1 23 10 10
3
2 14 10 2
2
I am using int([time1]]*24) , I use 24 because it represents the 24
periods in a 24 hour clock. Ideally I would have like to display thehour as
00:00, 01:00, 02:00 etc but I can live with the representation of 1 Being
the first hour and so on.
Andy B
SELECT Val([Time1])\100 AS TheHour, Count([Time1]) AS
TheCount
FROM [YourTable]
GROUP BY Val([Time1])\100;
--
Duane Hookom
MS Access MVP
AndyB said:
Sorry I'll explain.
I have also tried the code that Mr. Hookom suggested and I get the same
results.
the results are.
The hour The count
0 388
I have 388 records in the database. I am looking for the query to yield
this.
The hour The count
00:00 3
01:00 7
02:00 4
03:00 9
Etc., [Time1] is the time of occurance that an incident
happened. so if 3 incidents happened between midnight and 1:00 am as above
It would be counted correctly.
Andy Benjamin
Duane Hookom said:
Usually when someone states "it did not work", they provide a little more
information. In addition to providing more complete information next time,
you might want to try:
SELECT [Time1]\100 AS TheHour, Count([Time1]) AS
TheCount
FROM [YourTable]
GROUP BY [Time1]\100;
--
Duane Hookom
MS Access MVP
Sorry , I gave it a try and it did not work. I even tried the
"Round"function a fiddled with it and that too did not work.
ANdy Benjamin
chris
wrote in message Try something like...
SELECT Int([Time1]/100) AS TheHour, Count([Time1]) AS
TheCount
FROM [YourTable]
GROUP BY Int([Time1]/100);
-----Original Message-----
I have a field in a table that is called Time1. It is
based on a 24 hour
clock ie., 1535 = 3:35 PM
I want a query that will chow home many events occur each
hour so if
something happened at say 1535 it would be counted at
1500. if something
happened at 1606 it would count as 1600.
I tried using a crosstab query, and even used the ROUND
command, but it did
not work all it did was give me all the TIMES of
occurance.
Any Suggestions?
Andy Benjamin
.
