Crosstab query

[Gus Chuch]

The standard access crosstab query uses the PIVOT Format([Date],â€mmmâ€)
In(“Janâ€,â€Febâ€,â€Marâ€,…) to make columns for the (12)months of the year, how
would you Format the Date for (52) weeks of the year?
The week should start on Sunday - right?

 

[Stefan Hoffmann]

The standard access crosstab query uses the PIVOT Format([Date],â€mmmâ€)
In(“Janâ€,â€Febâ€,â€Marâ€,…) to make columns for the (12)months of the year, how
would you Format the Date for (52) weeks of the year?
The week should start on Sunday - right?

Use the DatePart function to get the week, e.g.

PIVOT DatePart("ww", [Date]) IN (1, 2, ..., 53)

The 53 is not a typo, some years have 53 calendar weeks.


mfG
--> stefan <--

 

[Gus Chuch]

Works great, but how can you get the columns to have meaningful names?
--
thank You

The standard access crosstab query uses the PIVOT Format([Date],â€mmmâ€)
In(“Janâ€,â€Febâ€,â€Marâ€,…) to make columns for the (12)months of the year, how
would you Format the Date for (52) weeks of the year?
The week should start on Sunday - right?

Use the DatePart function to get the week, e.g.

PIVOT DatePart("ww", [Date]) IN (1, 2, ..., 53)

The 53 is not a typo, some years have 53 calendar weeks.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Gus,

Works great, but how can you get the columns to have meaningful names?

You can use the Caption property of the crosstab query. Open the query
in the design view and open the property editor.


mfG
--> stefan <--

 

[Duane Hookom]

I would think the week number would be meaningful. If you are displaying the
results in a form or report, you can create column headings with text boxes
bound to "meaningful" values.