Date order in a crosstab query

[July 07]

Hello all,

I am trying to create a crosstab query to look like the following:

Order# Total Hours Jan 07 Feb 07 Mar 07
1234 175 25 50 100

What I am getting is:

Order# Total Hours Jan 06 Jan 07 Jan 08 Jul 06 Jul 07 Jul 08
1234 175 25 50 100


I would like to sort them by Month / year where the month data is totaled -
individual days are in the table I am pulling from through a query

any help is apprecaited.

 

[Lord Kelvan]

because it is sorting by alpha

it appears to us as a date but that isnt a date format so access
treats it as text so it is working properly

i had a simmiler problem with a totally different thing but what i did
was i converted the word jan to 01-07 and it worked

there may be another way but i dont know sorry

 

[Dale Fye]

If you open your query in design view, open up the properties window, then
click in the grey area next to the tables in the top of the box, you will see
a "Column Headings" property for the query. Fille in that property like:

Column Headings: "Jan 06", "Feb 06", "Mar 06", ...

When you are done, take a look at the SQL. What it does is add a PIVOT
clause at the end of the query:

PIVOT [FieldName] IN ("Jan 06", "Feb 06", ....)

You have to be careful though, if the value you enter in the Pivot clause is
not in the result set, then you will get an empty column. Also, if you
accidentally type "Feb O6" instead of "Feb 06", then you will get a blank
column as well.

If you have a dynamic query, that uses parameters to limit your query, you
will need to redefine the PIVOT clause. I do this by calling a function that
evaluates the fields that are returned by the query (without the pivot
clause), then creates the pivot clause and modifies the Pivot clause of the
original query. This way, I ensure that the column headers are correct, and
in the format I want them.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.