crosstab query

  • Thread starter Thread starter AndyB
  • Start date Start date
A

AndyB

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
 
Try something like...

SELECT Int([Time1]/100) AS TheHour, Count([Time1]) AS
TheCount
FROM [YourTable]
GROUP BY Int([Time1]/100);
 
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 said:
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



.
Click to expand...
Click to expand...
 
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


AndyB said:
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 said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
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


AndyB said:
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 said:
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



.
Click to expand...
Click to expand...
Click to expand...
 
Is Time1 a text or numeric field? What happens if you try:

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


AndyB said:
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
Click to expand...
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



.
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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



.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top