Query sorting problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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
..
 
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.
 
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
..
 
Back
Top