Crosstab Queries (Using a Wizard)

[Tamrak]

Hello,

I am trying to design a simple crosstab from a crosstab wizard. I have no problems creating the columns to be from January to December, (left to right.)

My problem is a simple one. The crosstab does not differentiate the June 2000, June 2001 or July 2000 and July 2001.. and so on.

My department plans to keep the data for 13 months before archiving it. I use the "sum" as the value.

How can I differentiate the months, if they are in the different year?

Is it possible to write an SQL or a query that will automatically roll the 13-month?

For example; July 00-August00-Sept00...... July 01

Then, when August 1 starts, the query can automatically start my 13-month rolling period:

For example: August00--.............. August 01

Thank you.

 

[JoeMiller]

You can change the format of the column that has "Value" in the crosstab row. If you look at the wizard's query you'll see something like this in the column:

The Value: Format([MyDate],"mmm&quot

Change it to this:

The Value: Format([MyDate],"mmm-yy&quot

Then you'll see the year and the month in the column headings.

Joe Miller
joe.miller@flotech.net

 

[Tamrak]

JoeMiller,

Thank you. I have one more problem.

When I tried to run the query, it was sorted by "alphabetical order." For example, April 01, August 00, June 00, June 01, May 00,

When I changed the format to "yy-mm", it gave me of what I want. However, it looked like this: "00-06","01-04","01-05."

I would like the format to read chronologically: Month-year such as Jun-00,Jul 00, Aug 00.... Jan 01, Feb 01. I do not know what is wrong.

Any advice? Thank you.

 

[JoeMiller]

You'll have to use the yy-mm to get the proper sort. I don't know of a way for it to know you're working with months and have it sort left to right.

Joe Miller
joe.miller@flotech.net