Learn how to create a dynamic monthly crosstab reports

wangling

Dynamic Monthly
Crosstab Report Try not to use "absolute" column headings for dates. You could possibly use "relative" dates.
This tip shows how to create monthly headings but will work just as well with other date intervals such as weeks, days, years, or other. For instance to change to weeks, change the DateDiff() and DateAdd() functions to use "ww" rather than "m".

For instance:

Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the query parameters by menuing: Query|Parameters and enter:
    Forms!frmA!txtEndDate     Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)
This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
Column Headings: "Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months.

If you need column labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...

< This solution requires no code and will run fairly quickly.

benhh

谢谢分享，不过我想知道把Crosstab用于某些商品的统计，并且商品是每月不同的，有什么方法能一劳永逸而不用经常修改报告？

xaxxax

kkkk