Is there a trick to not have a cross-tab query sort the column headings?

[pw]

Hi,

I have month names (coming from a field in a table) as the column
heading in an Access 97 crosstab query. It is being sorted
alphabetically. This will not do. The only way that I know to get
around it is to use month numbers instead, but I'd rather have the
names appear.

Any ideas?

Thanks,

-pw
please use "pw at williamsonenterprises dot com" for e-mail
thanks
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

[DFS]

pw,

Since you generally use the query as the datasource for a form or report,
reorder the columns in the bound fields of the form or report object.

If you use the form wizard and create a datasheet default form, it will look
just like the query results anyway, but you can move the columns around and
save the ordering.


"pw" <[e-mail]> wrote in message
news:tr********************************@4ax.com...

Hi,

I have month names (coming from a field in a table) as the column
heading in an Access 97 crosstab query. It is being sorted
alphabetically. This will not do. The only way that I know to get
around it is to use month numbers instead, but I'd rather have the
names appear.

Any ideas?

Thanks,

-pw
please use "pw at williamsonenterprises dot com" for e-mail
thanks
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==---- http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups ---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---

[pw]

>

1) Open your crosstab query in design mode.
2) Switch to SQL View
3) Find the PIVOT statement in the SQL statement.
4) After the field the PIVOT statement is working on, add in:

IN
("January","February","March","April","May","June ","July","August","September","October","November" ,"December")

(watch the word wrap!)

Change the order of the month names to get whatever order you
want/need. By manipulating the IN Clause of the PIVOT statement, you
also control the left-to-right order of the columns. This is a fairly
common "trick" to do (in code) to create reports that appear to always
have the last month as the first column, the month before that as the
2nd column... (aka "rolling columns")

Note: after making this change, and then switching back to "Design
mode", you won't see the in clause. Access doesn't show it, but it's
there, and it persists. (If memory serves, they do appear under the
"ColumnHeadings" property of the field you have selected for a column
heading.)

Hi Chuck!

It worked! Except putting in the month name blanks out the zeroes
that were in columns(months) that didn't have any records for that
month. I need the zeroes in any cell that doesn't have any data for
it. The user is going to copy and paste this pivot table into Excel
(I offered to try and do it automatically through Office Automation
but he doesn't mind just copy and pasting it).

Any ideas?

Thanks!

-paul



On Tue, 16 Dec 2003 15:32:38 -0700, pw
<[e-mail]> wrote:

I have month names (coming from a field in a table) as the column
heading in an Access 97 crosstab query. It is being sorted
alphabetically. This will not do. The only way that I know to get
around it is to use month numbers instead, but I'd rather have the
names appear.

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

[pw]

>pw,

Since you generally use the query as the datasource for a form or report,
reorder the columns in the bound fields of the form or report object.

If you use the form wizard and create a datasheet default form, it will look
just like the query results anyway, but you can move the columns around and
save the ordering.


I haven't thought what I am going to do with this query yet :-) Well,
I guess I have. It needs to be somewhere where the user can copy and
paste it into Excel.

I'll mess around with a form and try reordering the columns. Chuck's
suggestion worked though, except adding the month names in code
blanked out any cells that had zeroes in them (unless the column had a
value > 1 somewhere). I think I need zeroes, not blanks.

Thanks!!

-pw


"pw" <[e-mail]> wrote in message
news:tr********************************@4ax.com.. .

Hi,

I have month names (coming from a field in a table) as the column
heading in an Access 97 crosstab query. It is being sorted
alphabetically. This will not do. The only way that I know to get
around it is to use month numbers instead, but I'd rather have the
names appear.

Any ideas?

Thanks,

-pw
please use "pw at williamsonenterprises dot com" for e-mail
thanks
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet

News==----

http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000

Newsgroups

---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption

=---

----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---

[pw]

>On Wed, 17 Dec 2003 11:01:17 -0700, pw

<[e-mail]> wrote:

1) Open your crosstab query in design mode.
2) Switch to SQL View
3) Find the PIVOT statement in the SQL statement.
4) After the field the PIVOT statement is working on, add in:
IN
("January","February","March","April","May","Ju ne","July","August","September","October","Novembe r","December")
(watch the word wrap!)
Change the order of the month names to get whatever order you
want/need. By manipulating the IN Clause of the PIVOT statement, you
also control the left-to-right order of the columns. This is a fairly
common "trick" to do (in code) to create reports that appear to always
have the last month as the first column, the month before that as the
2nd column... (aka "rolling columns")
Note: after making this change, and then switching back to "Design
mode", you won't see the in clause. Access doesn't show it, but it's
there, and it persists. (If memory serves, they do appear under the
"ColumnHeadings" property of the field you have selected for a column
heading.)

Hi Chuck!
It worked! Except putting in the month name blanks out the zeroes
that were in columns(months) that didn't have any records for that
month. I need the zeroes in any cell that doesn't have any data for
it. The user is going to copy and paste this pivot table into Excel
(I offered to try and do it automatically through Office Automation
but he doesn't mind just copy and pasting it).

Unfortunately, there's really no way around that. Well, there are a
couple of ways, neither of which do I use unless I have to, and that's
pretty darn rare. Personally, I just do my own export to excel when I
have to do this, and use the NZ function as a part of that code to
translate the Nulls. (If you haven't seen my "ExportToExcel"
function, which I've posted here a couple of times, I suggest getting
it. You can put it behind any form and just let the user click on a
button to run the query and create a Excel spreadsheet. I can post it
again, if needed, but....)

The other (non-recommended methods) involve using a query (or a set of
queries) or a table that will ensure that you won't have any "missing"
information for the cross-tab.

Learning lots from you Chuck. I searched this group in Google and I
see a bunch of posts of "ExportToExcel" but I didn't see you name on
them. I want to make sure I get the original :-)

Thanks again,

-paul
----== Posted via Newsfeed.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeed.com The #1 Newsgroup Service in the World! >100,000 Newsgroups
---= 19 East/West-Coast Specialized Servers - Total Privacy via Encryption =---