date time querie

[john.9.williams]

hi,

i have a table with data, one of the columns hase the date and time
entered into it, what i am trying to do is run a qurie that tells me
how many rows of data are within a minute of the previous row of data.
i am stuck

 

[scubadiver]

It would require a sub-query.

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[John Spencer]

PERHAPS something like the following will give you what you want.

SELECT A.DateField, Count(B.DateField) as CountPrior
FROM YourTable as A INNER JOIN YourTable as B
ON A.DateField > B.DateField and
A.DateField < DateAdd("s",60,B.DateField)
Group By A.DateField

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County