Changing Crosstab Column Heading Order

[Guest]

I am looking to find a simple way to arrange the column headings in my
crosstab query in order.
The query is used to determine total amount of bills to be paid each day, by
date.
Vendors down, due dates across the top.
For example:
| 1/15/05 | 1/16/05 | 1/18/05 |...
Vend1 | $15.00 | | $22.00 |...
Vend2 | $34.00 | $42.00| |...

The query is perfect but the dates across the top are never in order
(Chronologically).
The dates also change frequently so entering fixed column headings will not
work. This is used as is, not in a form or report, so I do need it to
display correctly as a query.
Any suggestions?

 

[Duane Hookom]

Next time, please share your SQL view. If the date values are all in the
same year, you can try set the column heading expression to something like:
ColHead: Format([DateField],"mm/dd/yy")

 

[Guest]

Hi Duane,
Thanks for the response.
Here is the SQL :
TRANSFORM Sum([Peachtree Purchase History Summary by week].Total) AS [The
Value]
SELECT [Peachtree Purchase History Summary by week].[Vendor Name],
Sum([Peachtree Purchase History Summary by week].Total) AS [Total Of Total]
FROM [Peachtree Purchase History Summary by week]
GROUP BY [Peachtree Purchase History Summary by week].[Vendor Name]
PIVOT [Peachtree Purchase History Summary by week].Date;

I have tried different date formats for the headers, but the order in how
they display across the top never changes.
The only way I have been able to change the order is physically drag and
drop each in order or enter the manual values.

To answer your question, yes the values will span years. I have also
updated the query that feeds this crosstab to group by week instead of by
day. The column headers now read Jan 2005 (3), to show third week in Jan of
2005, etc. This also works fine for me.
The problem remains that the first column shown is Apr 2006 and then jumps
to Dec 2005 and so on.
Is there some kind of assend or decend by header option?
Thanks again

Next time, please share your SQL view. If the date values are all in the
same year, you can try set the column heading expression to something like:
ColHead: Format([DateField],"mm/dd/yy")

--
Duane Hookom
MS Access MVP
--

I am looking to find a simple way to arrange the column headings in my
crosstab query in order.
The query is used to determine total amount of bills to be paid each day,
by
date.
Vendors down, due dates across the top.
For example:
| 1/15/05 | 1/16/05 | 1/18/05 |...
Vend1 | $15.00 | | $22.00 |...
Vend2 | $34.00 | $42.00| |...

The query is perfect but the dates across the top are never in order
(Chronologically).
The dates also change frequently so entering fixed column headings will
not
work. This is used as is, not in a form or report, so I do need it to
display correctly as a query.
Any suggestions?

 

[Duane Hookom]

I would have expected your PIVOT to look as I suggested
PIVOT Format([Peachtree Purchase History Summary by week].Date,"mm/dd/yyyy")

If you want to include multiple years, use
PIVOT Format([DateValue],"yyyy-mm-dd")

If the results go to report, there is a better method.

Your other option is to set the Column Headings property of the crosstab
with DAO code.

--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the response.
Here is the SQL :
TRANSFORM Sum([Peachtree Purchase History Summary by week].Total) AS [The
Value]
SELECT [Peachtree Purchase History Summary by week].[Vendor Name],
Sum([Peachtree Purchase History Summary by week].Total) AS [Total Of
Total]
FROM [Peachtree Purchase History Summary by week]
GROUP BY [Peachtree Purchase History Summary by week].[Vendor Name]
PIVOT [Peachtree Purchase History Summary by week].Date;

I have tried different date formats for the headers, but the order in how
they display across the top never changes.
The only way I have been able to change the order is physically drag and
drop each in order or enter the manual values.

To answer your question, yes the values will span years. I have also
updated the query that feeds this crosstab to group by week instead of by
day. The column headers now read Jan 2005 (3), to show third week in Jan
of
2005, etc. This also works fine for me.
The problem remains that the first column shown is Apr 2006 and then jumps
to Dec 2005 and so on.
Is there some kind of assend or decend by header option?
Thanks again

Next time, please share your SQL view. If the date values are all in the
same year, you can try set the column heading expression to something
like:
ColHead: Format([DateField],"mm/dd/yy")

--
Duane Hookom
MS Access MVP
--

I am looking to find a simple way to arrange the column headings in my
crosstab query in order.
The query is used to determine total amount of bills to be paid each
day,
by
date.
Vendors down, due dates across the top.
For example:
| 1/15/05 | 1/16/05 | 1/18/05 |...
Vend1 | $15.00 | | $22.00 |...
Vend2 | $34.00 | $42.00| |...

The query is perfect but the dates across the top are never in order
(Chronologically).
The dates also change frequently so entering fixed column headings will
not
work. This is used as is, not in a form or report, so I do need it to
display correctly as a query.
Any suggestions?

 

[Guest]

HI Duane,
I think the basic rule I didn’t know was that Access already puts the
headers in alpha-numeric order, reading from left to right within the name of
the header. You just have to change the format accordingly. In other words,
you have to make the date format numeric and reverse the order (yyyy-mm-dd)
to make the column chronological from left to right (oldest is left, newest
is right).
This also means the text date formats will not work.
I have a few off the wall questions that I am just curious about.
Is there a way to have a sort field and display field for the column headers?
Can you reverse the internal ordering in Access so the newest column starts

I would have expected your PIVOT to look as I suggested
PIVOT Format([Peachtree Purchase History Summary by week].Date,"mm/dd/yyyy")

If you want to include multiple years, use
PIVOT Format([DateValue],"yyyy-mm-dd")

If the results go to report, there is a better method.

Your other option is to set the Column Headings property of the crosstab
with DAO code.

--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the response.
Here is the SQL :
TRANSFORM Sum([Peachtree Purchase History Summary by week].Total) AS [The
Value]
SELECT [Peachtree Purchase History Summary by week].[Vendor Name],
Sum([Peachtree Purchase History Summary by week].Total) AS [Total Of
Total]
FROM [Peachtree Purchase History Summary by week]
GROUP BY [Peachtree Purchase History Summary by week].[Vendor Name]
PIVOT [Peachtree Purchase History Summary by week].Date;

I have tried different date formats for the headers, but the order in how
they display across the top never changes.
The only way I have been able to change the order is physically drag and
drop each in order or enter the manual values.

To answer your question, yes the values will span years. I have also
updated the query that feeds this crosstab to group by week instead of by
day. The column headers now read Jan 2005 (3), to show third week in Jan
of
2005, etc. This also works fine for me.
The problem remains that the first column shown is Apr 2006 and then jumps
to Dec 2005 and so on.
Is there some kind of assend or decend by header option?
Thanks again

Next time, please share your SQL view. If the date values are all in the
same year, you can try set the column heading expression to something
like:
ColHead: Format([DateField],"mm/dd/yy")

--
Duane Hookom
MS Access MVP
--

I am looking to find a simple way to arrange the column headings in my
crosstab query in order.
The query is used to determine total amount of bills to be paid each
day,
by
date.
Vendors down, due dates across the top.
For example:
| 1/15/05 | 1/16/05 | 1/18/05 |...
Vend1 | $15.00 | | $22.00 |...
Vend2 | $34.00 | $42.00| |...

The query is perfect but the dates across the top are never in order
(Chronologically).
The dates also change frequently so entering fixed column headings will
not
work. This is used as is, not in a form or report, so I do need it to
display correctly as a query.
Any suggestions?

 

[Duane Hookom]

You can sort by one field and display another in a crosstab column heading.
I doubt you can reverse the order of the derived columns.

I generally display the results in reports. If this is your desire, there
are much better methods of doing this using relative dates. Your column
headings would be derived from a calculation like:
ColHead: "D" & DateDiff("d", [Date], Date())

--
Duane Hookom
MS Access MVP

HI Duane,
I think the basic rule I didn't know was that Access already puts the
headers in alpha-numeric order, reading from left to right within the name
of
the header. You just have to change the format accordingly. In other
words,
you have to make the date format numeric and reverse the order
(yyyy-mm-dd)
to make the column chronological from left to right (oldest is left,
newest
is right).
This also means the text date formats will not work.
I have a few off the wall questions that I am just curious about.
Is there a way to have a sort field and display field for the column
headers?
Can you reverse the internal ordering in Access so the newest column
starts

I would have expected your PIVOT to look as I suggested
PIVOT Format([Peachtree Purchase History Summary by
week].Date,"mm/dd/yyyy")

If you want to include multiple years, use
PIVOT Format([DateValue],"yyyy-mm-dd")

If the results go to report, there is a better method.

Your other option is to set the Column Headings property of the crosstab
with DAO code.

--
Duane Hookom
MS Access MVP

Hi Duane,
Thanks for the response.
Here is the SQL :
TRANSFORM Sum([Peachtree Purchase History Summary by week].Total) AS
[The
Value]
SELECT [Peachtree Purchase History Summary by week].[Vendor Name],
Sum([Peachtree Purchase History Summary by week].Total) AS [Total Of
Total]
FROM [Peachtree Purchase History Summary by week]
GROUP BY [Peachtree Purchase History Summary by week].[Vendor Name]
PIVOT [Peachtree Purchase History Summary by week].Date;

I have tried different date formats for the headers, but the order in
how
they display across the top never changes.
The only way I have been able to change the order is physically drag
and
drop each in order or enter the manual values.

To answer your question, yes the values will span years. I have also
updated the query that feeds this crosstab to group by week instead of
by
day. The column headers now read Jan 2005 (3), to show third week in
Jan
of
2005, etc. This also works fine for me.
The problem remains that the first column shown is Apr 2006 and then
jumps
to Dec 2005 and so on.
Is there some kind of assend or decend by header option?
Thanks again


:

Next time, please share your SQL view. If the date values are all in
the
same year, you can try set the column heading expression to something
like:
ColHead: Format([DateField],"mm/dd/yy")

--
Duane Hookom
MS Access MVP
--

I am looking to find a simple way to arrange the column headings in
my
crosstab query in order.
The query is used to determine total amount of bills to be paid each
day,
by
date.
Vendors down, due dates across the top.
For example:
| 1/15/05 | 1/16/05 | 1/18/05 |...
Vend1 | $15.00 | | $22.00 |...
Vend2 | $34.00 | $42.00| |...

The query is perfect but the dates across the top are never in order
(Chronologically).
The dates also change frequently so entering fixed column headings
will
not
work. This is used as is, not in a form or report, so I do need it
to
display correctly as a query.
Any suggestions?