Please don't double/triple post the same question.
Here’s one way to do it (see attached). When prompted, enter 072011 as the MonthYear parameter.
However, there are several concepts in this. The first point is I have assumed that the batching plants remain fixed and therefore the headers remain fixed.
The first query (qryCalcWeeks) is used to calculate a WkDayID and a WeekNum from your source data.
The second query (qryMonthCal) uses the data from the first query and also a table (tblDays) to generate what the whole month should look like. In other words it generates extra records for the days you don’t require information for (the days before and after the 1st and last days of the month). This provides a template for the month.
The third query (qryMonthResults) generates the full months records using the template month query and the first query. If you run it you can see the empty records have been created. Also see how a WeekDesc has been created from the WeekNum (to look nice).
The final query is a crosstab (qryReport). If you take a look you can see how it pivots on the batchingPlants so that the batching plants appear as columns. Note that this is a parameter query that ensures that MonthYear is available to the report.
The report (rptSolution) is fixed in layout and uses the crosstab query as its source, grouped by WeekDesc. The production targets are obtained using DLookups. The coloured formatting of Sunday that you see when you run the report in Print Preview, is done in VBA using the On Format event for the detail section. Take a look at the code to see how it works.
As I said, this is just one way to do this. Another way could be to create sub reports which would make the report more dynamic i.e. the column headers would not need to be fixed.
hth
Chris
