By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,095 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,095 IT Pros & Developers. It's quick & easy.

Sort Report on Calculated Field

P: n/a
I've been given a database and asked to figure out how to sort a
report. The report needs to be sorted on a field that is the sum of a
field that is calculated in the underlying query. NetSales is
calculated in the query; the control source of the text box on the
report is Sum([NetSales]). I need to sort on this. I tried going to
the query and creating a new field - TotalNetSales: Sum([NetSales]),
but I get an error message about "cannot have aggregate function
in...clause (Sum([NetSales])". Is there a way to do this?

TIA,
JD
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Not sure about this, but, in the report's (design view)
Grouping/Sorting dialog box put this in one of the Expression fields:

=Sum(NetSales)

& set its Sorting field to Ascending or Descending.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZacoechKqOuFEgEQJcxwCZAZlakYljpT9QR69gvsedd1/ZhYwAn0uL
Fq52d4OvCrqI3MsOjHAXXZQn
=8m/p
-----END PGP SIGNATURE-----
jd****@yahoo.com wrote:
I've been given a database and asked to figure out how to sort a
report. The report needs to be sorted on a field that is the sum of a
field that is calculated in the underlying query. NetSales is
calculated in the query; the control source of the text box on the
report is Sum([NetSales]). I need to sort on this. I tried going to
the query and creating a new field - TotalNetSales: Sum([NetSales]),
but I get an error message about "cannot have aggregate function
in...clause (Sum([NetSales])". Is there a way to do this?


Nov 12 '05 #2

P: n/a
Thanks for the reply, but when I tried this, I got an error message:
"Cannot have aggregate function in ORDER BY clause (Sum([NetSales])."
Any other suggestions?

MGFoster <me@privacy.com> wrote in message news:<GT*******************@newsread2.news.pas.ear thlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Not sure about this, but, in the report's (design view)
Grouping/Sorting dialog box put this in one of the Expression fields:

=Sum(NetSales)

& set its Sorting field to Ascending or Descending.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZacoechKqOuFEgEQJcxwCZAZlakYljpT9QR69gvsedd1/ZhYwAn0uL
Fq52d4OvCrqI3MsOjHAXXZQn
=8m/p
-----END PGP SIGNATURE-----

Nov 12 '05 #3

P: n/a
You can always compute the sum (and fill all other fields) in one
query, then use it as the source query for another one that runs the
report, sorting by the sum field.

On 4 Mar 2004 06:13:51 -0800, jd****@yahoo.com (jd****@yahoo.com)
wrote:
Thanks for the reply, but when I tried this, I got an error message:
"Cannot have aggregate function in ORDER BY clause (Sum([NetSales])."
Any other suggestions?

MGFoster <me@privacy.com> wrote in message news:<GT*******************@newsread2.news.pas.ear thlink.net>...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Not sure about this, but, in the report's (design view)
Grouping/Sorting dialog box put this in one of the Expression fields:

=Sum(NetSales)

& set its Sorting field to Ascending or Descending.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQEZacoechKqOuFEgEQJcxwCZAZlakYljpT9QR69gvsedd1/ZhYwAn0uL
Fq52d4OvCrqI3MsOjHAXXZQn
=8m/p
-----END PGP SIGNATURE-----


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.