By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Checking a condition to be true over 9 consecutive months

P: n/a
jr
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If the baby was born in the time frame predicted by your doctor you can say
first, the mother is not a virgin and, second, the mother was pregnant over
nine consecutive months.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"jr" <jr************@virgin.net> wrote in message
news:Jl*****************@newsfe7-win.ntli.net...
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
Donnie, are you impersonating Steve now; or, Steve, have you absorbed too
much of Donnie's blather?

"PC Datasheet" <no****@nospam.spam> wrote in message
news:HM*******************@newsread1.news.atl.eart hlink.net...
If the baby was born in the time frame predicted by your doctor you can
say
first, the mother is not a virgin and, second, the mother was pregnant
over
nine consecutive months.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"jr" <jr************@virgin.net> wrote in message
news:Jl*****************@newsfe7-win.ntli.net...
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


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.