calculate average per week

Question

I have a table in ms-access that keeps data about incoming daily calls. Normally someone can answer 5-7 per day. One of the fields is named "completed_date". When this date is not null that means the call has been answered/completed.

How can I return the average call answer per week?

Answer 1

Use the DatePart Function to compute week numbers based on your completed_date values.

SELECT
    DatePart("ww", completed_date) AS week_number,
    Count(*)
FROM YourTable
WHERE completed_date Is Not Null
GROUP BY DatePart("ww", completed_date);

That could work for data within a single calendar year. However, if your data spans more than one year, include Year(completed_date) as an additional field expression and GROUP BY item.

Note DatePart() accepts optional arguments so you can specify firstdayofweek and firstweekofyear. If your organizations week numbering strategy isn't compatible with DatePart(), you can create a calendar table to map dates and week numbers, then join the calendar table with your original data source table.