Dates in Consecutive Quarters

[fishtek]

I'm trying to figure out a query statement that would identify when 2 dates occur within consecutive quarters (but not the same quarter). I've been messing around with DatePart and DateAdd but not getting very far. Thanks for any help.

 

[dhookom]

Try use DateDiff("Q",date1,date2) = 1

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

In Oracle you can do: [tt]SELECT TO_CHAR(SYSDATE, 'Q') FROM dual[/tt]
Access should take [tt]Format(YourDateFieldHere,"q") [/tt]



Have fun.

---- Andy

 

[dhookom]

Andy,
I think there is a need to consider the year since Format(#4/1/2012#,"Q) = 2 and Format(#7/1/2013#,"Q") = 3 but clearly these are not consecutive quarters.

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Thanks dhookum, can I use a variation of DateDiff("Q",date1,date2) = 1 to give me a count of ocurrences in a list of dates. In other words, I have a list of dates on a report where an event occurs and I would like Access to tell me how many times the event occurs in consecutive quarters.
Thanks again for your assistance.

 

[BigRed1212]

I would like Access to tell me how many times the event occurs in consecutive quarters

So if an event occurs in the first two quarters of year one and in the last three quarters of year two, is the answer to your question two (meaning there were two periods in which the event occurred in consecutive quarters) or three (it occurred in quarter one and quarter two in year one, quarter two and quarter three in year two, and quarter three and quarter four in year two)?

 

[dhookom]

I agree with BigRed1212. We need some sample data and specifics on whether the condition is met.

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

Thanks guys. See the example below. The result would be a test fail that occurred in three consecutive quarters with a total of 5 fail events. Again, I am looking for Access to look at an array of dates and tell me how may fails occurred in consecutive quarters. In this case 3.

Thanks

2009
1/1/2009 Fail
4/1/2009 Fail
7/22/2009 Pass
12/5/2009 Pass


2010
2/3/2010 Pass
4/30/2010 Fail
8/25/2010 Fail
11/3/2010 Fail

 

[dhookom]

Is there only one record per quarter? What happens if there are Pass records in the same quarter?

Are there any other fields that need to be considered?

Duane
Hook'D on Access
MS Access MVP

 

[fishtek]

There will only be one record per quarter and I am only concerned about the test date.

thanks

 

[BigRed1212]

Given this data:

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

This query:

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 
)

returns:

con_fails
3

 

[fishtek]

Thanks BigRed1212:

I'm having trouble making this code work. I tried it in the SQL window of the query but keep getting error messages. Can this code be modified to work in a module then called up from a report?
Thanks Again

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 
)

 

[BigRed1212]

keep getting error messages."


What do they say?