Strange Behaviour in Crosstab

[Rob Parker]

Duan Hookom posted a link (http://www.tek-tips.com/faqs.cfm?fid=4524) to a
method for generating a multi-column xtab in response to a question
yesterday. It took my interest, so I had a play with it, using the
Northwind database. In the course of this, I found a problem when I tried
changing the format of the ColumnHeading field - and this applies also to a
straight cross-tab, not just the multi-column one.

For example, the following query returns three rows (one for each of the
three years of data in the Northwind database), with columns headed 1 to 12
for each of the months:

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.ProductName, Year([OrderDate]) AS [Year]
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID
WHERE (((Products.ProductName)="alice mutton"))
GROUP BY Products.ProductName, Year([OrderDate])
PIVOT Month([OrderDate]);

I decided that I would prefer to see the Month headings as Jan, Feb, etc,
rather than as numbers, so I changed the PIVOT clause to:

PIVOT Format(Month([OrderDate]),"mmm")

The crosstab now returns only two month columns, Dec and Jan. The Dec
column actually contains the January data, and the Jan column contains the
sum of the data for all other months. I hadn't bothered setting the month
order with an IN clause; however, doing so has no effect - the only columns
with data are Dec and Jan; all other month columns are empty.

Why is this happening? And how can I get text-style months as column
headings, rather than numbers? I'm surprised I've never noticed this before,
as I use this sort of technique regularly.

In case it matters: using Access 2002 SP3, under Windows XP Pro SP2.

TIA,

Rob

 

[Duane Hookom]

You mis-used the Format() function. You should not convert the date to month
and then format. For instance Month(#12/14/2006#) returns 12. Placing 12 in
the Format() function:
Format(12,"mmm")
returns the month of the 12th day following 12/30/1899 which is Jan.
Don't work so hard, use:
PIVOT Format([OrderDate],"mmm")

 

[Rob Parker]

Thanks Duane,

It must be the impending holiday season. I think my brain's gone on leave
already ;-)

Rob

You mis-used the Format() function. You should not convert the date to
month
and then format. For instance Month(#12/14/2006#) returns 12. Placing 12
in
the Format() function:
Format(12,"mmm")
returns the month of the 12th day following 12/30/1899 which is Jan.
Don't work so hard, use:
PIVOT Format([OrderDate],"mmm")
--
Duane Hookom
Microsoft Access MVP

Duan Hookom posted a link (http://www.tek-tips.com/faqs.cfm?fid=4524) to
a
method for generating a multi-column xtab in response to a question
yesterday. It took my interest, so I had a play with it, using the
Northwind database. In the course of this, I found a problem when I
tried
changing the format of the ColumnHeading field - and this applies also to
a
straight cross-tab, not just the multi-column one.

For example, the following query returns three rows (one for each of the
three years of data in the Northwind database), with columns headed 1 to
12
for each of the months:

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.ProductName, Year([OrderDate]) AS [Year]
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID
WHERE (((Products.ProductName)="alice mutton"))
GROUP BY Products.ProductName, Year([OrderDate])
PIVOT Month([OrderDate]);

I decided that I would prefer to see the Month headings as Jan, Feb, etc,
rather than as numbers, so I changed the PIVOT clause to:

PIVOT Format(Month([OrderDate]),"mmm")

The crosstab now returns only two month columns, Dec and Jan. The Dec
column actually contains the January data, and the Jan column contains
the
sum of the data for all other months. I hadn't bothered setting the
month
order with an IN clause; however, doing so has no effect - the only
columns
with data are Dec and Jan; all other month columns are empty.

Why is this happening? And how can I get text-style months as column
headings, rather than numbers? I'm surprised I've never noticed this
before,
as I use this sort of technique regularly.

In case it matters: using Access 2002 SP3, under Windows XP Pro SP2.

TIA,

Rob

 

[David F Cox]

PIVOT Format([OrderDate],"mmm")

 

[Rob Parker]

Thanks David,

Duane has already pointed out where my brain has faded ;-)

Rob

PIVOT Format([OrderDate],"mmm")

Duan Hookom posted a link (http://www.tek-tips.com/faqs.cfm?fid=4524) to
a method for generating a multi-column xtab in response to a question
yesterday. It took my interest, so I had a play with it, using the
Northwind database. In the course of this, I found a problem when I
tried changing the format of the ColumnHeading field - and this applies
also to a straight cross-tab, not just the multi-column one.

For example, the following query returns three rows (one for each of the
three years of data in the Northwind database), with columns headed 1 to
12 for each of the months:

TRANSFORM Sum([Order Details].Quantity) AS SumOfQuantity
SELECT Products.ProductName, Year([OrderDate]) AS [Year]
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID
WHERE (((Products.ProductName)="alice mutton"))
GROUP BY Products.ProductName, Year([OrderDate])
PIVOT Month([OrderDate]);

I decided that I would prefer to see the Month headings as Jan, Feb, etc,
rather than as numbers, so I changed the PIVOT clause to:

PIVOT Format(Month([OrderDate]),"mmm")

The crosstab now returns only two month columns, Dec and Jan. The Dec
column actually contains the January data, and the Jan column contains
the sum of the data for all other months. I hadn't bothered setting the
month order with an IN clause; however, doing so has no effect - the only
columns with data are Dec and Jan; all other month columns are empty.

Why is this happening? And how can I get text-style months as column
headings, rather than numbers? I'm surprised I've never noticed this
before, as I use this sort of technique regularly.

In case it matters: using Access 2002 SP3, under Windows XP Pro SP2.

TIA,

Rob