Query Weeks Over Multiple Years

[ewong1]

I have a database that I am using to report metrics based on seasonally available data. What I am running into is the problem that the seasonally entered data doesn't start on the same date every year.

An example:
I have data that was entered into my database last year starting July 26, 2004. This is considered part of week 1 of my season and I want to compare that data to records that started being entered on August 15, 2005. Which is week 1 of the 2005 season.

Records include the following information: id, date, other irrelevant details

Does anyone have any ideas on how I might be able to accomplish this? Thanks in advance for your help!

 

[fliflo]

Maybe you can do a new table that has two fields.

(the date where it begins the first week of that year) and (the week number of the year)

then yo can use that table in queries to compare the weeks.