467,075 Members | 1,005 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,075 developers. It's quick & easy.

sorting a crosstab query

I would like to sort the results of a crosstab query by the aggregate
function it automatically generates (total of the values in each column of
the crosstab).

If I simply put "ascending" in the sort field of the design view I get the
following error message:
"cannot have aggregate function in order by clause".

I had no success trying to edit the sql statement directly, either.
I looked quite a while for help inside access or online but cannot find any
solution, I hope you can point out what I am missing.

Thanks in advance,
Luca

PS: I can run the query, go to the datasheet view and then sort the desired
field but each time I rerun the query the sorting is lost. I need the query
result to be automatically sorted without user intervention.
Nov 13 '05 #1
  • viewed: 6076
Share:
2 Replies
How about saving your crosstab query without the sorting and then making a
new query that uses your crosstab query as the source and applies the
sorting?

HTH

David

"luca varani" <lv*****@stanford.edu> wrote in message
news:dh**********@news.Stanford.EDU...
I would like to sort the results of a crosstab query by the aggregate
function it automatically generates (total of the values in each column of
the crosstab).

If I simply put "ascending" in the sort field of the design view I get the
following error message:
"cannot have aggregate function in order by clause".

I had no success trying to edit the sql statement directly, either.
I looked quite a while for help inside access or online but cannot find any solution, I hope you can point out what I am missing.

Thanks in advance,
Luca

PS: I can run the query, go to the datasheet view and then sort the desired field but each time I rerun the query the sorting is lost. I need the query result to be automatically sorted without user intervention.

Nov 13 '05 #2
"David Mann" <dm***@ic24.net> wrote in message
news:rF***************@newsfe1-win.ntli.net...
How about saving your crosstab query without the sorting and then making a
new query that uses your crosstab query as the source and applies the
sorting?


I arrived to the same solution last night, after much frustration and wasted
time. I'd still like to know if there is a way to sort the crosstab query
but the double query works fine.

Thanks a lot !
Luca
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Sherman H. | last post: by
4 posts views Thread by Judy | last post: by
7 posts views Thread by newguy@yahoo.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.