ORDER BY a Calculated field

B

Brenda

I'm learning SQL, and I'm trying to write a query that sorts by a calculated
value.

The table Widgets has two fields, WidgetID and TimeToCompletion. I'm
calculating MyNewVariable as TimeToCompletion + 5. I want to sort by the new
variable. When I do it in DesignView in Access, I get this SQL code:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5 AS
MyNewVar
FROM Widgets
ORDER BY [TimeToCompletion]+5;

Is it possible to use MyNewVar in the ORDER BY statement? Access doesn't
seem to like it when I put in MyNewVar (it treats it like a parameter and
asks for a value). In this case it is rather simple to use the formula
[TimeToCompletion]+5, but I have a project I'm working on where the
calculation is several lines long and it gets quite messy.

Thanks,

Brenda

P.S. Access doesn't seem to have any tool to make SQL "pretty," so I can
find problems with nested parens, etc. Does anyone know of an extension to
Access that makes the SQL easier to edit? Thanks...
 
B

Brendan Reynolds

The way you have it now is the way it is done, Brenda - repeat the
calculation in the ORDER BY clause.

Re how to sort on more complex expressions - well, whenever possible, don't
do it. It's likely to be slow - potentially very slow when dealing with
large volumes of data across a network. Whenever possible, try to sort on an
indexed column, it will be much faster.

When you really must sort on the result of an expression, use copy and paste
to replicate the expression in the ORDER BY clause - the Access SQL editor
is, unfortunately, primitive, but the standard text-editing keyboard
shortcuts, including Ctrl+C and Ctrl+V, do work.

Occasionally, when editing complex SQL, I resort to copying the entire SQL
statement, pasting it into an external text editor, then copying and pasting
back into Access SQL view when I've finished editing.
 
B

Brenda

Thanks for your help. I think I may end up creating a new table with the
results of the query, and then sorting the new table.

....Brenda

Brendan Reynolds said:
The way you have it now is the way it is done, Brenda - repeat the
calculation in the ORDER BY clause.

Re how to sort on more complex expressions - well, whenever possible,
don't do it. It's likely to be slow - potentially very slow when dealing
with large volumes of data across a network. Whenever possible, try to
sort on an indexed column, it will be much faster.

When you really must sort on the result of an expression, use copy and
paste to replicate the expression in the ORDER BY clause - the Access SQL
editor is, unfortunately, primitive, but the standard text-editing
keyboard shortcuts, including Ctrl+C and Ctrl+V, do work.

Occasionally, when editing complex SQL, I resort to copying the entire SQL
statement, pasting it into an external text editor, then copying and
pasting back into Access SQL view when I've finished editing.

--
Brendan Reynolds

Brenda said:
I'm learning SQL, and I'm trying to write a query that sorts by a
calculated value.

The table Widgets has two fields, WidgetID and TimeToCompletion. I'm
calculating MyNewVariable as TimeToCompletion + 5. I want to sort by the
new variable. When I do it in DesignView in Access, I get this SQL code:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5
AS
MyNewVar
FROM Widgets
ORDER BY [TimeToCompletion]+5;

Is it possible to use MyNewVar in the ORDER BY statement? Access doesn't
seem to like it when I put in MyNewVar (it treats it like a parameter and
asks for a value). In this case it is rather simple to use the formula
[TimeToCompletion]+5, but I have a project I'm working on where the
calculation is several lines long and it gets quite messy.

Thanks,

Brenda

P.S. Access doesn't seem to have any tool to make SQL "pretty," so I can
find problems with nested parens, etc. Does anyone know of an extension
to Access that makes the SQL easier to edit? Thanks...
Click to expand...
Click to expand...
 
6

'69 Camaro

Hi, Brenda.
Is it possible to use MyNewVar in the ORDER BY statement?
Click to expand...

No. It doesn't exist in the data source, Widgets. Try:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5 AS
MyNewVar
FROM Widgets
ORDER BY 3;

.. . . because 3 is the third column in this query.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Brenda said:
I'm learning SQL, and I'm trying to write a query that sorts by a
calculated value.

The table Widgets has two fields, WidgetID and TimeToCompletion. I'm
calculating MyNewVariable as TimeToCompletion + 5. I want to sort by the
new variable. When I do it in DesignView in Access, I get this SQL code:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5 AS
MyNewVar
FROM Widgets
ORDER BY [TimeToCompletion]+5;

Is it possible to use MyNewVar in the ORDER BY statement? Access doesn't
seem to like it when I put in MyNewVar (it treats it like a parameter and
asks for a value). In this case it is rather simple to use the formula
[TimeToCompletion]+5, but I have a project I'm working on where the
calculation is several lines long and it gets quite messy.

Thanks,

Brenda

P.S. Access doesn't seem to have any tool to make SQL "pretty," so I can
find problems with nested parens, etc. Does anyone know of an extension to
Access that makes the SQL easier to edit? Thanks...
Click to expand...
 
B

Brenda

Yay! That works!

Thanks,

Brenda


'69 Camaro said:
Hi, Brenda.
Is it possible to use MyNewVar in the ORDER BY statement?
Click to expand...

No. It doesn't exist in the data source, Widgets. Try:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5 AS
MyNewVar
FROM Widgets
ORDER BY 3;

. . . because 3 is the third column in this query.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.


Brenda said:
I'm learning SQL, and I'm trying to write a query that sorts by a
calculated value.

The table Widgets has two fields, WidgetID and TimeToCompletion. I'm
calculating MyNewVariable as TimeToCompletion + 5. I want to sort by the
new variable. When I do it in DesignView in Access, I get this SQL code:

SELECT Widgets.WidgetID, Widgets.TimeToCompletion, [TimeToCompletion]+5
AS
MyNewVar
FROM Widgets
ORDER BY [TimeToCompletion]+5;

Is it possible to use MyNewVar in the ORDER BY statement? Access doesn't
seem to like it when I put in MyNewVar (it treats it like a parameter and
asks for a value). In this case it is rather simple to use the formula
[TimeToCompletion]+5, but I have a project I'm working on where the
calculation is several lines long and it gets quite messy.

Thanks,

Brenda

P.S. Access doesn't seem to have any tool to make SQL "pretty," so I can
find problems with nested parens, etc. Does anyone know of an extension
to Access that makes the SQL easier to edit? Thanks...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

updating a sorted numerical field to absolute position in recordset? 2
Referencing a Caculated Field in another Calculated Field 4
Complicated Calculated Field Logic 0
Summing multiple fields 2
Order of evaluation of functions in records & somewhat recursivefunction 4
Fixing up field with a row number to become PK 1
Sort on a calculated field 1
SELECT vs. ORDER BY - order of execution question 7

Top