Frequency Distribution

[arfasih]

Hello,

I have a simple table, it has two coloumns [Product] [DateTime],

in this table row updates in random time (it could update every second or no updates for several seconds).

How can I get a frequency distribution (number of product) in evry 10 seconde ?

Product,DateTime
A 10/15/2009 12:34:01
B 10/15/2009 12:34:03
B 10/15/2009 12:34:08
C 10/15/2009 12:37:29
A 10/15/2009 12:43:25
A 10/15/2009 12:44:25
B 10/15/2009 12:44:25
D 10/15/2009 12:44:25

query must return something like this:

3
0
0
1
3
0

Thank you in advance,
Regards,
Fasih

 

[PWise]

First you need a table defining your 10 second intervels
[tt]Startintrval endintrval
10/15/9 00:00:00 10/15/9 00:00:09
10/15/9 00:00:10 10/15/9 00:00:19
..................
10/15/9 23:59:40 10/15/9 23:59:49
10/15/9 23:59:50 10/15/9 23:59:59
[/tt]
left join intrval table to productdate table

select Startintrval ,Sum(iif(product,>"",1,0))
from(
Select *
From intrval
left join  productdate
on datetime between Startintrval And endintrval

)dt
Group by Startintrval [/code]

 

[dhookom]

You could also create a table of date and a table of time intervals and build a cartesian query to get all time intervals for all dates. There are other solutions for deriving all dates and time intervals but you might want to keep it simple with pwise's suggestion.

Duane
Hook'D on Access
MS Access MVP