sorting a crosstab query

luca varani

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

David Mann

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

luca varani

"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