Crosstab Report

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

Hi,

I have a table on which I would like to produce the following report (All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date range
that is chosen the weekNo changes and therefore the report will not run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it is an
every changing field?

Thanks
 
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 for this but people need to see the Week No.
Is there no way of doing this?
Could I code something in the on format of the report?
eg taking the entered date and then working backwards to get the week number
and putting it in a unbound textbox?
What do you?
Any help greatly appreciated

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


news.microsoft.com said:
Hi,

I have a table on which I would like to produce the following report (All
fields are in the table
WeekNo
Stockcode, Date sum(Qty)

I can do this but the problem I have is that dependent on the date range
that is chosen the weekNo changes and therefore the report will not run
because the control for the week no is the actual weekno.

How can I have a std report that displays the correct weekno when it is an
every changing field?

Thanks
Click to expand...
Click to expand...
 
Use the same type of expressions for you column headings in the report. Use
text boxes with control sources like:
=DateAdd("ww",0,[Forms]![frmA]![txtEndDate])
=DateAdd("ww",-1,[Forms]![frmA]![txtEndDate])
=DateAdd("ww",-2,[Forms]![frmA]![txtEndDate])
This should create column "labels" that vary depending on the date entered
on the form.

--
Duane Hookom
MS Access MVP
--

news.microsoft.com said:
Thanks for this but people need to see the Week No.
Is there no way of doing this?
Could I code something in the on format of the report?
eg taking the entered date and then working backwards to get the week number
and putting it in a unbound textbox?
What do you?
Any help greatly appreciated

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