Sort Report on Calculated Field

[jdph40]

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

[MGFoster]

-----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?

[jdph40]

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 <[e-mail]> 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-----

[Larry Johnson]

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 <[e-mail]> 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-----