Dates In a CrossTab query

[Box 666]

What I would like to do is to end up with a report that shows the number of
items sold per week. Where the list of goods goes down the left side of the
report and across the top the number of items sold in the last 12 weeks.
...but I do not want to keep altering the query or report each week in order
to achieve this.

In the original query I can continually get the last 12 weeks by using
">=Date()-84", I am then basing a crosstab query on this original query
(weeks is not an option with the wizard so I used months) and by going into
the SQL view I find I can change the "mmm" to "ww" and "Jan" to "1" etc, but
this does mean I have to set the parameters for all 52 weeks and when you
view the results you see 12 weeks populated and the remainder blank. (whilst
this is not a problem at the query stage, it looks daft in the report)

If you then base a report on this query whilst it will be correct for this
week (you would only show the 12 populated weeks) next week you have one
blank week (the oldest now the 13th week) and be missing the latest week
altogether.

Is there a way you can detail in the query or on the report to show it as
[current week], [current week]-1,[current week]-2, etc. If so how.

With thanks

Bob

 

[Duane Hookom]

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.
To create column labels 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])
....