Counting values in time period 1

[arfasih]

Hello,

I have a table with two fields. products and Datetime.
how can I count the number of products on each hour ?
from 6:00 AM until 6:00 PM for example.

Table 1
---Products---|---DateTime----
A 2009-10-20 6.00.01
A 2009-10-20 6.10.10
B 2009-10-20 6.30.31
C 2009-10-20 7.00.41
C 2009-10-20 7.10.21
B 2009-10-20 7.35.31
B 2009-10-20 7.50.05
A 2009-10-20 8.00.20
A 2009-10-20 8.24.04
B 2009-10-20 9.04.02


Output is like this table:

---TimePriod----|---Product Count----
6-7 3
7-8 4
8-9 2

instead of this time period format, I can assign simple numbers as a index.


Thank you so much,

 

[randy700]

How about...

SELECT DatePart("h",[DateTime]) & "-" & DatePart("h",[DateTime]) + 1 AS TimePeriod, Count(Product) AS ProductCount
FROM Table1
GROUP BY DatePart("h",[DateTime]) & "-" & DatePart("h",[DateTime])+1;

Randy

 

[arfasih]

Thank you so much dear randy700.
you made my day.