Crosstab Report and query

  • Thread starter Thread starter Newbie
  • Start date Start date
N

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;
 
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.
 
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 said:
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 said:
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;
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
query
or
report.


--
Duane Hookom
MS Access MVP
--

Newbie said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Thanks - works a treat!
Duane Hookom said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
query
or
report.


--
Duane Hookom
MS Access MVP
--

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
Click to expand...
Click to expand...
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
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top