On Sun, 24 Jul 2005 15:21:13 GMT, "jr" <jr************@virgin.net> wrote:
A perplexing one this.
I Am trying to design a query or series of queries which will firstly
identify a condition.
If column A value is less than column B value make column C value =1 ,
else 0.
This is easy enough
Then I want to identify all rows where column C = 1 ,
no problem so far
However I then want to find in resultant dataset if the rows have
consecutive monthly values.
ie row 2 column D - row 1 column D = between 28 and 31. ?????
If there is a break in the sequence before 9 is met ie row 8 column d - row
7 column d - Then we start again from 1 on the next comparison.
Can it be done with Access 2002 - Or will I be writing a good old VB module
Hi
Say you have a query called "runstrue" with just one field "monthnumber", which is the months
for which some condition is true. (I assume months are a single increasing set of numbers).
Define a table "mynumbers" with a single field "mynum", with values 1, 2, 3 etc upto at least
9 in your case. (Make it the primary key).
Then this query will show you all runs (sequences) of 9 months for which the condtion is true:
SELECT mynumbers_1.mynum AS runlength, mynumbers.mynum AS startmonth
FROM mynumbers, mynumbers AS mynumbers_1, runstrue
WHERE (((runstrue.monthnumber) Between [mynumbers].[mynum] And [mynumbers].[mynum]+[mynumbers_1].[mynum]-1))
GROUP BY mynumbers_1.mynum, mynumbers.mynum
HAVING (((mynumbers_1.mynum)>=9) AND ((Count(runstrue.monthnumber))=[mynumbers_1].[mynum]))
ORDER BY mynumbers_1.mynum;
It may not run very quickly if tables are big!
Most "SQL" methods for detecting runs in data use proprietary features of different vendors, eg mdiff for Teradata.
David
