Crosstab Report and query

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;

This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate. Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the query or
report.


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
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;

Thanks but . . . .
how will this cope with my requirement to report all weeks that are earlier
than the 11 weeks I want to report all being added into 1 column labelled
overdue?

Based on my query what do I need to do?

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;

Thanks again for your help

"Duane Hookom" wrote in message
...
This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form
to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate.
Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the query
or
report.


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
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;

Using my suggestion, I would set the column heading expression to something
like:
ColHead:"Wk" & IIf(DateDiff("ww", [YourDate], Forms!frmA!txtEndDate) 11,
"11", Datediff("ww",[YourDate], [Forms]![frmA]![txtEndDate]))


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
Thanks but . . . .
how will this cope with my requirement to report all weeks that are
earlier
than the 11 weeks I want to report all being added into 1 column labelled
overdue?

Based on my query what do I need to do?

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;

Thanks again for your help

"Duane Hookom" wrote in message
...
This is where I would use "relative weeks" rather than "absolute weeks".
Relative weeks would compare your date field to a date entered on a form
to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as txtEndDate.
Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the same
column/fields. It requires no code and no future maintenance of the
query
or
report.


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
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;

Thanks - works a treat!
"Duane Hookom" wrote in message
...
Using my suggestion, I would set the column heading expression to
something
like:
ColHead:"Wk" & IIf(DateDiff("ww", [YourDate], Forms!frmA!txtEndDate) 11,
"11", Datediff("ww",[YourDate], [Forms]![frmA]![txtEndDate]))


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
Thanks but . . . .
how will this cope with my requirement to report all weeks that are
earlier
than the 11 weeks I want to report all being added into 1 column
labelled
overdue?

Based on my query what do I need to do?

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;

Thanks again for your help

"Duane Hookom" wrote in message
...
This is where I would use "relative weeks" rather than "absolute
weeks".
Relative weeks would compare your date field to a date entered on a
form
to
create the columns. For instance, you could use an expression like:
ColHead:"Wk" & Datediff("ww",[DivDate],[Forms]![frmA]![txtEndDate])
This would create column headings like
"Wk0", "Wk1", "Wk2", "Wk3",...
Wk0 would contain the values for DivDate in the same week as
txtEndDate.
Wk6
would be 6 weeks earlier. You would need to set the Query|Parameters
[Forms]![frmA]![txtEndDate] Date/Time
and set the Column Headings property to
"Wk0", "Wk1", "Wk2", "Wk3",... (depends on how many weeks)
This method allows you to create a report that will ALWAYS have the
same
column/fields. It requires no code and no future maintenance of the
query
or
report.


--
Duane Hookom
MS Access MVP
--

"Newbie" wrote in message
...
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;