Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
I would like Access to tell me how many times the event occurs in consecutive quarters
ID testdate results
1 1/1/2009 Fail
2 4/1/2009 Fail
3 7/22/2009 Pass
4 12/5/2009 Pass
5 2/3/2010 Pass
6 4/30/2010 Fail
7 8/25/2010 Fail
8 11/3/2010 Fail
select sum(streak) as con_fails
from
(
SELECT
iif(a.results=b.results, 1,0) as streak
from
testresults a,
testresults b
where a.results ='fail'
and datediff ("q", b.testdate, a.testdate) =1
)
con_fails
3
select sum(streak) as con_fails
from
(
SELECT
iif(a.results=b.results, 1,0) as streak
from
testresults a,
testresults b
where a.results ='fail'
and datediff ("q", b.testdate, a.testdate) =1
)