Crosstab Help

[Newbie]

Hi,

I have the following query - see below

I want a report that is based on week number - the actual week number needs
to be on the report.

I have written a query that inserts all the relevant records into a table
incl. the week number where it substitues anything that is older than 12
weeks before the entered date as a week 0
eg. date = 19/5/04 = week 21
date = 23/2/04 = week 0

I want my report to display 12 weeks prior to the date entered on a form
plus the Week 0

At the moment if a particular week doesn;t have any data it is missed out
completely.

How can I ensure that all 12 weeks will be reported?

Here is the crosstab query that I have

PARAMETERS [forms]![frmMetalRequirements]![dtpDateTo] DateTime;
TRANSFORM Sum(tblMatlSchedule.OSQtyReqd) AS SumOfOSQtyReqd
SELECT tblMatlSchedule.JobNo, tblMatlSchedule.Stockcode,
tblMatlSchedule.StartDate, Sum(tblMatlSchedule.OSQtyReqd) AS [Total Of
OSQtyReqd]
FROM tblMatlSchedule
GROUP BY tblMatlSchedule.JobNo, tblMatlSchedule.Stockcode,
tblMatlSchedule.StartDate
PIVOT tblMatlSchedule.WeekNo;