Extract data by week numbers for rolling 10 weeks

[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?

 

[Dale Fye]

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]

Thanks,

This seems to do the trick

Sue

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.

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?