Need to query weekly data

[cfisher440]

I want to query weekly data.
To be more specific, I am using MS Access for now, but planning on converting it to MS SQL Server 2005. We go by the 4-5-4 calendar as well. I have a table with a date column that has the format dd-mm-yy. I want to do queries based off the weekly data for each month. What is the best way of doing that?

 

[giadich]

you will need to create some sort of calendar table and group the dates into 4-5-4 format. join your data table to this view base on the dates then you can do queries to group the data by week.

your table calendar should have something like: theyear, themonth, theweek, fiscalyear, fiscalmonth, fiscalweek, thedate and maybe weeknumber if you want to do 1-52 weeks...