I've got a table that monitors the letters we send to the clients. We're also monitoring the letter status within this table, and particularly, you want to know the number of individuals who received instructions decided to be approached again.

This part is simple, however I've been requested to group the outcomes by pre-specified days (i.e. Week 1, Week 2, etc). I've been utilizing a switch statement for this function, however that people have been in the twentieth approximately week, MS Access says the totally too complex.

Here's my code - using the switch statement simplified with regard to brevity. We really have days 1-20 in the switch statement, which is what's leading to Use of not process the query.

SELECT Count([Letter Status].Patient_ID) AS CountOfPatient_ID, Switch([Date_Returned] Between #10/25/2011# And #10/31/2011#,"Week 1") AS Week
FROM [Letter Status]
WHERE ((([Letter Status].Letter_Status)="Agreed to be contacted"))
GROUP BY Switch([Date_Returned] Between #10/25/2011# And #10/31/2011#,"Week 1");

Because the switch statement is not working, I'm wondering if there is a far more logical method for processing the outcomes by Week. Our temporary solution involves me processing 1 week at any given time, and adding the outcomes to some separate table. However, I am confident it goes against db normalization, and when there's an easy method to get it done, I must learn.

Help.

You can group using datepart rather.

E.g.,

GROUP BY datepart("ww", Date_Returned)

When the week amounts came back by Access don't match the amounts you'll need, just add or take away an offset number as necessary.

I believe DatePart() will be a good fit for any standard calendar. However yours appears unusual.

? WeekdayName(Weekday(#10/25/2011#))
Tuesday

? WeekdayName(Weekday(#10/31/2011#))
Monday

Therefore it seems your calendar week begins with Tuesday and runs with the following Monday. You are able to adjust for "firstdayofweek" within the DatePart() function ...

? DatePart("ww", #10/25/2011#, vbTuesday)
 44 

... however you'll still have to convert a few days number DatePart() provides you with to fit your calendar's week numbering strategy.

Which raises the question of how can you determine the date for the beginning of week #one in your calendar. Could it be always the 4th Tuesday in October? The final Tuesday in October? Another thing?

If you are not able to easily express your calendar business rules with VBA, It may be simpler to punt. Produce a calendar table having a row for every date and extra fields for accounting year and week. Then join [Letter Status] table using the calendar table to complete your letter counting by calendar week.

should you used datediff making use of your first week because the base, something similar to

WeekNo = datediff("ww","1/7/2011",Date_Returned)

this can keep incrementing and also you wont be concerned concerning the change of the year.