Setting order in crosstab query

R

Ruth Isaacs

Hello All

I have:

TRANSFORM Sum([credit]-[debit]) AS [value]
SELECT Format([date],"mmm yyyy") AS Expr1
FROM [payedoc accounts]
GROUP BY Format([date],"mmm yyyy")
PIVOT [payedoc accounts].category;

.... which works OK, except that the rows are being displayed in alphabetical
order according to the expression
Format([date],"mmm yyyy") AS Expr1 i.e.
Apr 1998 followed by Apr 1999 followed by Apr 2000 etc

whereas I need them to be displayed in date order i.e.
Apr 1998 followed by May 1998 followed by June 1998 etc.

I really cannot see how to do this although I'm sure it must be possible.
BTW: I am aware that I have broken a golden rule in having a field name
'date' - but I have got away with it so far!

Hope someone can help.
Many thanks
Leslie Isaacs
 
X

x-rays

why don't you try Select Cstr(Month([date])) + " " + Cstr(Year
([date]))
GROUP BY Cstr(Month([date])) + " " + Cstr(Year ([date]))

Just guessing did not test it.
 
L

Leslie Isaacs

Hello 'X-rays'

Thanks for your reply, but unfortunately it didn't work: it just gave me the
followinmg order of rows:

1 1998
1 1999
1 2000
.... (then after 1 2006)
2 1998
2 1999
etc

Somehow I need to get the query to recognise that the string that I create
as the row heading actually represents a date - or at least a month and a
year, so that it can then sort the rows according to that date.

Hope you (or anyone!) can help.
Many thanks
Les




x-rays said:
why don't you try Select Cstr(Month([date])) + " " + Cstr(Year
([date]))
GROUP BY Cstr(Month([date])) + " " + Cstr(Year ([date]))

Just guessing did not test it.

Ruth said:
Hello All

I have:

TRANSFORM Sum([credit]-[debit]) AS [value]
SELECT Format([date],"mmm yyyy") AS Expr1
FROM [payedoc accounts]
GROUP BY Format([date],"mmm yyyy")
PIVOT [payedoc accounts].category;

... which works OK, except that the rows are being displayed in
alphabetical
order according to the expression
Format([date],"mmm yyyy") AS Expr1 i.e.
Apr 1998 followed by Apr 1999 followed by Apr 2000 etc

whereas I need them to be displayed in date order i.e.
Apr 1998 followed by May 1998 followed by June 1998 etc.

I really cannot see how to do this although I'm sure it must be possible.
BTW: I am aware that I have broken a golden rule in having a field name
'date' - but I have got away with it so far!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
 
X

x-rays

Hello leslie,

what if you do: Select Format([date],"mmm yyyy")
GROUP BY Format([date],"mmm yyyy")
PIVOT bla bla
ORDER BY Cstr(Year([date])) + " " + Cstr(Month
([date]))

not sure where order by goes, give it a try.

Leslie said:
Hello 'X-rays'

Thanks for your reply, but unfortunately it didn't work: it just gave me the
followinmg order of rows:

1 1998
1 1999
1 2000
... (then after 1 2006)
2 1998
2 1999
etc

Somehow I need to get the query to recognise that the string that I create
as the row heading actually represents a date - or at least a month and a
year, so that it can then sort the rows according to that date.

Hope you (or anyone!) can help.
Many thanks
Les




x-rays said:
why don't you try Select Cstr(Month([date])) + " " + Cstr(Year
([date]))
GROUP BY Cstr(Month([date])) + " " + Cstr(Year ([date]))

Just guessing did not test it.

Ruth said:
Hello All

I have:

TRANSFORM Sum([credit]-[debit]) AS [value]
SELECT Format([date],"mmm yyyy") AS Expr1
FROM [payedoc accounts]
GROUP BY Format([date],"mmm yyyy")
PIVOT [payedoc accounts].category;

... which works OK, except that the rows are being displayed in
alphabetical
order according to the expression
Format([date],"mmm yyyy") AS Expr1 i.e.
Apr 1998 followed by Apr 1999 followed by Apr 2000 etc

whereas I need them to be displayed in date order i.e.
Apr 1998 followed by May 1998 followed by June 1998 etc.

I really cannot see how to do this although I'm sure it must be possible.
BTW: I am aware that I have broken a golden rule in having a field name
'date' - but I have got away with it so far!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try

ORDER BY Format([Date],"yyyymm")

Otherwise, October, November, and December will come right after January.

x-rays said:
Hello leslie,

what if you do: Select Format([date],"mmm yyyy")
GROUP BY Format([date],"mmm yyyy")
PIVOT bla bla
ORDER BY Cstr(Year([date])) + " " + Cstr(Month
([date]))

not sure where order by goes, give it a try.

Leslie said:
Hello 'X-rays'

Thanks for your reply, but unfortunately it didn't work: it just gave me
the
followinmg order of rows:

1 1998
1 1999
1 2000
... (then after 1 2006)
2 1998
2 1999
etc

Somehow I need to get the query to recognise that the string that I
create
as the row heading actually represents a date - or at least a month and a
year, so that it can then sort the rows according to that date.

Hope you (or anyone!) can help.
Many thanks
Les




x-rays said:
why don't you try Select Cstr(Month([date])) + " " + Cstr(Year
([date]))
GROUP BY Cstr(Month([date])) + " " + Cstr(Year ([date]))

Just guessing did not test it.

Ruth Isaacs wrote:
Hello All

I have:

TRANSFORM Sum([credit]-[debit]) AS [value]
SELECT Format([date],"mmm yyyy") AS Expr1
FROM [payedoc accounts]
GROUP BY Format([date],"mmm yyyy")
PIVOT [payedoc accounts].category;

... which works OK, except that the rows are being displayed in
alphabetical
order according to the expression
Format([date],"mmm yyyy") AS Expr1 i.e.
Apr 1998 followed by Apr 1999 followed by Apr 2000 etc

whereas I need them to be displayed in date order i.e.
Apr 1998 followed by May 1998 followed by June 1998 etc.

I really cannot see how to do this although I'm sure it must be
possible.
BTW: I am aware that I have broken a golden rule in having a field
name
'date' - but I have got away with it so far!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 
L

Leslie Isaacs

John
Many thanks - that worked great!
Les

John Spencer said:
Try

ORDER BY Format([Date],"yyyymm")

Otherwise, October, November, and December will come right after January.

x-rays said:
Hello leslie,

what if you do: Select Format([date],"mmm yyyy")
GROUP BY Format([date],"mmm yyyy")
PIVOT bla bla
ORDER BY Cstr(Year([date])) + " " + Cstr(Month
([date]))

not sure where order by goes, give it a try.

Leslie said:
Hello 'X-rays'

Thanks for your reply, but unfortunately it didn't work: it just gave me
the
followinmg order of rows:

1 1998
1 1999
1 2000
... (then after 1 2006)
2 1998
2 1999
etc

Somehow I need to get the query to recognise that the string that I
create
as the row heading actually represents a date - or at least a month and
a
year, so that it can then sort the rows according to that date.

Hope you (or anyone!) can help.
Many thanks
Les




why don't you try Select Cstr(Month([date])) + " " + Cstr(Year
([date]))
GROUP BY Cstr(Month([date])) + " " + Cstr(Year ([date]))

Just guessing did not test it.

Ruth Isaacs wrote:
Hello All

I have:

TRANSFORM Sum([credit]-[debit]) AS [value]
SELECT Format([date],"mmm yyyy") AS Expr1
FROM [payedoc accounts]
GROUP BY Format([date],"mmm yyyy")
PIVOT [payedoc accounts].category;

... which works OK, except that the rows are being displayed in
alphabetical
order according to the expression
Format([date],"mmm yyyy") AS Expr1 i.e.
Apr 1998 followed by Apr 1999 followed by Apr 2000 etc

whereas I need them to be displayed in date order i.e.
Apr 1998 followed by May 1998 followed by June 1998 etc.

I really cannot see how to do this although I'm sure it must be
possible.
BTW: I am aware that I have broken a golden rule in having a field
name
'date' - but I have got away with it so far!

Hope someone can help.
Many thanks
Leslie Isaacs
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Sorting in Crosstab 3
UNION and Crosstab query 5
Group by Range in Crosstab Query 4
sorting columns in crosstab 6
CrossTab Query Format 2
Sorting Crosstab Query 2
Crosstab - Multiple values on each row 5
Crosstab qry not adding up 1

Top