Multiple SQL Select Statements or DLookup Function in the same Report

[hangonavocado]

I have a Report that shows the following:

1) Name of Claim Processor
2) Total Claims Processed
3) Total Errors in Claims Processed

The criteria for the select query underlying the report is a date range (ProcessedDate) which is entered from a form that the end user accesses. This date range is typically for a month, for example 05/01/2013 through 05/31/2013.
Here is the issue I'm having trouble with, the user wants to see items 1, 2, and 3 above summarized by the WEEK in addition to a grand tally for the month (or whatever the date range is).

I have no problem getting the individual dates for each week within the month/date range, however, I can't figure out a way to dispay the grouping by week separately on the same report. Is it possible to have multiple text boxes with each text box's control source set to a different DLookup function or SQL Select query that restricts by the weekly dates? For example:

Week of 5/6/13 thru 5/10/13 Processor | #Claims | #Errors

Week of 5/13/13 thru 5/17/13 Processor | #Claims | #Errors

Week of 5/20/13 thru 5/24/13 Processor | #Claims | #Errors



Week of 5/27/13 thru 5/31/13 Processor | #Claims | #Errors

Thanks in advance for any ideas on how I can approach this!

[Dal Jeanis]

You can use Datepart("ww",ClaimDate,[FirstDayofWeek]) AS WeekNumber to calculate the week number from the ClaimDate field, and GROUP BY that WeekNumber field. [FirstdayofWeek] is one of (vbSunday, vbMonday...vbSaturday).

I'm not sure off the top of my head what the code would be to calculate the starting and ending formatted dates of that week from the week number. Whatever it was, you would override the start calc with IIF([StartofWeekCalc] < StartofMonth, StartofMonth, [StartofWeekCalc]) and do the reverse for the end calc.

[June7]

Also, with DatePart might want to specify the first week of year. The default is week starting January 1 (vbFirstJan1. Other options are:

vbFirstFourDays
vbFirstFullWeek
vbUseSystem

Depending on choice, could have weeks at beginning and end of year that are less then 7 days, which is fine if you don't want to pull data crossing years.

To calculate begin/end of week for a given date (does not consider change of year):

The first day of the current week (assuming Sunday = day 1):
somedate - WeekDay(somedate) + 1

The last day of the current week:
somedate - WeekDay(somedate) + 7

Code to get first day of week using week number: http://bytes.com/topic/access/answer...te-monday-week

[hangonavocado]

Thank you everyone for your insights, this is really helpful, I didn't know about the vbFirstFourDaysOption in DatePart, I will check that out.
I think I'm pretty close to a solution on this but now I'm stuck on an expression.
So I went with a report based on a crosstab query that is structured like this:

Supervisor (Group by Row Heading)
| Manager (Group by Row Heading) |
Expression: DateAdd("d",1-Weekday([ProcessedDate],2),[ProcessedDate])
Group by Column Heading |
CountofClaims Sum of Value |
TotalCount of Claims Sum of Row Heading

The part giving me problems is the expression: DateAdd("d",1-Weekday([ProcessedDate],2),[ProcessedDate])
That expression will return the date of the Monday before Sunday.

So weeks run from Monday to Sunday. How do I make the week run from Monday to Friday only? I am trying to sum by week for weekdays the # of claims done by processer by supervisor and manager. For example, I know the week of 5/13/13 (Monday) thru 5/17/13 (Friday) a particular processor completed 40 claims, however, using this function, it shows that the processor completed 50 claims for the week of 5/13/13. Others processors show 60 per week, when it should be no more than 50.
I am so close on this and would really appreciate any help on the expression syntax, thank you!

[June7]

If you want to exclude Sat and Sun records, calculate another field for the day of week and apply filter criteria. Here is one way:

DayOfWeek: Format([ProcessedDate],"ddd")

<> "Sat" And <> "Sun"

[hangonavocado]

Thank you thank you thank you! That worked