Extracting weekly figures automatically in MS Access
In the past whenever I've wanted to compute weekly figures from an MS Access database I have always had to specify the start and end dates of that week. This became very time consuming, especially when I also had to compare daily and weekly totals.
So I created this query to extract the day and week automatically.
1. Prompt the User to enter the date for the day of interest
2. Calculate the weekday number of the date entered, e.g. 26/6/11 is weekday 1, as it is a Sunday.
3. Calculate the Start Date of the week by taking the weekday number away from the date of interest, then add 1.
4. Calculate the End Date of the week by taking the weekday number away from the date of interest, then add 7.
5. Ensure that all date values are stored with Date formats.
I then used this query whenever I needed to extract a week's worth of information, by using the Start Date and End Date values in the formula 'Between[Start Date] And [End Date]'. The bonus of this is that I can extract data for both the day and week with only having to enter a single date. I imagine something similar could be developed in SAS.
