470,815 Members | 3,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Sort Report on Calculated Field

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
3 8256
-----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
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
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.

Similar topics

2 posts views Thread by Steven Stewart | last post: by
6 posts views Thread by DS | last post: by
3 posts views Thread by kelley.l.turner | last post: by
1 post views Thread by Nelly | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.