Extract data by week numbers for rolling 10 weeks

  • Thread starter Thread starter SueD
  • Start date Start date
S

SueD

I am running Access 2000 and need help extracting a rolling 10 weeks worth of
data.

I have a table containing (amongst other things) a column that I need to
query on called WEEK NO, which contains a numeric value showing the week for
which the data was estimated. Problem is how to extract the data for the
current week and the next 9 weeks.

I have acheived this so far by building a query and adding the criteria
"DatePart("ww",Now())" to the WEEK NO to find data from the current week.

I have added "Or DatePart("ww",Now())+1" etc up to "Or
DatePart("ww",Now())+9" to include the whole 10 weeks worth of data. I know
this is long winded but I tried to use >< symbols and failed.

Near the end of the year, the week numbers for January will go back to 1
again, so this query will fail to include them in my 10 weeks block.

Is there a better way to do this and one that will not fail when at year end?
 
Sue,

Try something like:

WHERE ([Week No] BETWEEN Datepart("ww", Now())
AND (Datepart("ww", Now()) + 10) )
OR ([Week No] < DatePart("ww", Now()) + 10 - 52)

If you are in week, 50, this should be interpreted as

WHERE [Week No] BETWEEN 50 and 60
OR [Week No] < 8

Do you have a [Year No] field as well? If so then the WHERE clause ought to
look something like:

WHERE ([Year No] = Datepart("yyyy", Now()) AND
[Week No] BETWEEN Datepart("ww", Now())
AND (Datepart("ww", Now()) + 10) )
OR ([Year No] = Datepart("yyyy", Now()) + 1 AND
[Week No] < DatePart("ww", Now()) + 10 - 52)


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Thanks,

This seems to do the trick

Sue

Dale Fye said:
Sue,

Try something like:

WHERE ([Week No] BETWEEN Datepart("ww", Now())
AND (Datepart("ww", Now()) + 10) )
OR ([Week No] < DatePart("ww", Now()) + 10 - 52)

If you are in week, 50, this should be interpreted as

WHERE [Week No] BETWEEN 50 and 60
OR [Week No] < 8

Do you have a [Year No] field as well? If so then the WHERE clause ought to
look something like:

WHERE ([Year No] = Datepart("yyyy", Now()) AND
[Week No] BETWEEN Datepart("ww", Now())
AND (Datepart("ww", Now()) + 10) )
OR ([Year No] = Datepart("yyyy", Now()) + 1 AND
[Week No] < DatePart("ww", Now()) + 10 - 52)


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



SueD said:
I am running Access 2000 and need help extracting a rolling 10 weeks worth of
data.

I have a table containing (amongst other things) a column that I need to
query on called WEEK NO, which contains a numeric value showing the week for
which the data was estimated. Problem is how to extract the data for the
current week and the next 9 weeks.

I have acheived this so far by building a query and adding the criteria
"DatePart("ww",Now())" to the WEEK NO to find data from the current week.

I have added "Or DatePart("ww",Now())+1" etc up to "Or
DatePart("ww",Now())+9" to include the whole 10 weeks worth of data. I know
this is long winded but I tried to use >< symbols and failed.

Near the end of the year, the week numbers for January will go back to 1
again, so this query will fail to include them in my 10 weeks block.

Is there a better way to do this and one that will not fail when at year end?
Click to expand...
Click to expand...
 
Back
Top