Query sorting problem

[Guest]

hi
I'm having a problem sorting a query and I'm not sure why. The query's
fields are :

ID, A1,A2,A3,A_fin, B1,B2,B3,B_fin, C1, .... , D3,D_fin, TOT_fin

The 'lettered' fields ending in ' _fin' are calculated fields, like this:
A_fin: A1 + A2 - A3
Finally, TOT_fin: A_fin + B_fin + C_fin + D_fin

Here is my prob. From time to time I need to be able to sort by one of the
five '_fin' fields in descending order. They all work fine except
TOT_fin??. When I try to sort by this field I get four 'parameter' requests
for A_fin to D_fin. How can I sort on the TOT_fin field?

By the way, all five of the '_fin' fields must be numerical.

Thanks greatly

 

[Guest]

Sarah,

The problem is that you cannot use a computed field in a GroupBy or OrderBy
clause of your query.

You can get around this by saving the query, then using it as the data
source of another, something like:

SELECT A1,A2, ......TOT_fin
FROM query1
ORDER BY TOT_fin

HTH
Dale

 

[John Spencer]

One other method that will work is to use the position of the Tot_Fin in the
select clause as part of the Order by Clause.

So if TOT_fin is the 38th field in the Select clause you can use

SELECT ...
FROM ...
WHERE ...
ORDER BY 38 Desc

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks to both Dale and John. I'll use your suggestions. Still, it is
interesing to me that I can sort by the computed fields A_fin, B_fin....
Only TOT_fin causes grief.

 

[Dale Fye]

John,

Never knew you could do that.

I love it when I pickup little tid-bits like this.

 

[John Spencer]

It works, but...

IF you should add a field to the beginning of the Select clause you will
probably need to adjust the order by clause - an easy thing to forget.
That's why my preferred method is to specify the field names.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..