Crosstab

[Boon]

Hello,

I am creating a crosstab query and would like to sort the column (or row) by
the column that is not displayed the data. Let me explain...

In a table I have field

Month_Name, Distribution_Name, Orders, Month_Num

The Month_Name looks like "Jan", "Feb",......
The Distribution_Name looks like "A","B",....
Orders is the number (125,650,...)
The Month_Num is 1,2,3,...

I want to build cross tab by putting Month_Name to column, Distribution_Name
to row and sum the orders (put it as value). Now the column in a result
datasheet does not sort in order (Jan, Feb, Mar), so I tried to sort by
Month_Num. But Access displays an error.

Is there a way to accomplish this or the work around on this?

Thank you,
Boon

 

[John Spencer]

You can specify the column names in the PIVOT clause of the query

Pivot Month_Name in ("Jan","Feb",...,"Nov","Dec")

If you are doing this in query design view, check the query properties
and enter the list of month names in order in the appropriate property.
I can't recall the property name at this time and I don't have access
to Access on this computer.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Access101]

In query design view, click View, Properties.

In the resulting list, Description, Default View, Column Headings ...

Enter the following in Column Headings:

Jan;Feb;Mar;Apr ... etc.

Let me know if that solves it

 

[KARL DEWEY]

Rather than using Month_Name and Month_Num use your DateTime field like this --
PIVOT Format([YourDateTimeField], "mmm") IN("JAN", "FEB", "MAR", "APR",
"MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC");

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When you want the columns headings sorted in some order other than
alphabetically or numerically you have to explicitly define that order:

PIVOT Month_Name In ("Jan", "Feb", "Mar", .... etc. ... )

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSXe02oechKqOuFEgEQJJSQCgjE8gl8GG3K9FSKjT/JM9sq0RNf0AoIzc
lMn7JzN2+a5EnyVxPhBEHmfh
=IwS+
-----END PGP SIGNATURE-----

 

[Boon]

Thankssssssssssssssssssss a lot to all answers.

I am able to do it now.

 

[Boon]

Thanks for all your answers.

How can I do this sorting with row heading?