Asking Access to count back 52 weeks from week/year specified

[madman]

I've been looking around the forum for about a half hour for a solution for my problem, but haven't had any luck. So here goes...

I have a table with multiple years of data, 2007, 2008, and 2009. I'd like to set-up a query that pulls 52 weeks of data back from the week/year I specify in my form to be current yr, and 52 weeks back from the last week of curr year to be prior yr. I normally deal with YTD timeframes but have had many requests for a 52wk vs. 52wk breakout. Below is an example of a reference table that I thought should work for the results I want. An example of what I'd be using it for is, if I were looking for a pound total for 52wk curr yr vs. 52wk prior yr.

Imagine this table being a lot larger with every week from multiple fiscal years.

numeric-value weekyear
for couting
1090 51 2008
1091 52 2008
1092 53 2008
1093 01 2009
1094 02 2009
1095 03 2009
1096 04 2009

my company's 2008 fiscal year had 53 weeks, normally our years have 52

Any help or ideas would be greatly appreciated!

 

[raskew]

Hi -

Try working with something like this:

>= dateadd("ww", -52, dateserial(year(date()),12,31))

HTH - Bob